SUMIF (Excel '97)

P

Phendrena

I want to sum a particular range using a date range as a criteria,
I have the dates in the worksheet formatted as dd/mm/yyyy.
How do i format this in the criteria?

For example dates for November would only be included anything else would be
ignored.

Thanks,
 
L

Learning Excel

Thanks for the formula Don Guillet and "Teethless mama", but I need to adjust
it to my worsheet wich the only difference than Phendrena's is that mine has
numbers from B1 to B30 and I need to ADD them ( not SUMPRODUCT) if A1 TO A30
is November 2007. Thanks.
 
B

Bob Phillips

=SUMPRODUCT(--(MONTH(A1:A30)=11),--(YEAR(A1:A30)=2007),B1:B30)

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
L

Learning Excel

For some reason, is not working.
To clarify my question:
In B1 to B30 I have values 2,3,5,9,80,95... that I' like the total on the
month of November ( in column A). Thanks
 
P

Phendrena

thanks for the replies,

So far i'm not having much luck, i have to say i am a novice when it comes
to most things on excel, so i thought i'd help if i gave you the formula to
see if you could suggest where i am going wrong:

=SUMIF('[name.xls]Mid-Term'!$B$3:$B$502,SUMPRODUCT(--(MONTH(B3:B503)=11),--(YEAR(B3:B503)=2007),B3:B503),'[name.xls]Mid-Term'!$G$3:$G$502)

Range: '[name.xls]Mid-Term'!$B$3:$B$502
Criteria: SUMPRODUCT(--(MONTH(B3:B503)=11),--(YEAR(B3:B503)=2007),B3:B503)
Sum Range: '[name.xls]Mid-Term'!$G$3:$G$502

Any further help would be most welcome.
 
P

Phendrena

Let me try and show you how the spreadsheet is setup:

Source Data:

Col B ..... ..... Col G
Date ...... .... Reason
Date ...... .... Reason

Date : is formatted as dd/mm/yyyy and the user presses the keyboard shortcut
(CTRL+;) to add in the date.
Reason : is just various text reasons that are picked from a drop-down menu,
all i need to do is just add up if the date if within a specific month
regardless of what text is there as long as there is text.

Summary Data:

Col B ..... ..... ...... Col D
Name 1 .... .... ... .... Data
Name 2 .... .... ... .... Data
Name 3 .... .... ... .... Data

So it's the Col D data field where i am going to pulling the information to
from the source (seperate workbooks).

Hope this helps with my query!!!

thanks,
 
D

David Biddulph

Look at what you've asked your formula to do.

You have asked it to add the values in column G on your Mid-Term sheet in
name.xls, for the rows where the value in column B on that sheet is equal to
the value of the specfied criterion, which is obtained from by multiplying
column B on your current sheet by two Booleans, one checking that the month
of the date in column B of your current sheet is November, and the other
checking that the year in that column is 2007. [And additionally you've got
unequal ranges, some going to row 502 and some to 503.]

I doubt whether that's what you intended. I guess that the SUMPRODUCT
formula was intended to give your answer, not to be used as the crierion in
the SUMIF formula.
--
David Biddulph

Phendrena said:
thanks for the replies,

So far i'm not having much luck, i have to say i am a novice when it comes
to most things on excel, so i thought i'd help if i gave you the formula
to
see if you could suggest where i am going wrong:

=SUMIF('[name.xls]Mid-Term'!$B$3:$B$502,SUMPRODUCT(--(MONTH(B3:B503)=11),--(YEAR(B3:B503)=2007),B3:B503),'[name.xls]Mid-Term'!$G$3:$G$502)

Range: '[name.xls]Mid-Term'!$B$3:$B$502
Criteria: SUMPRODUCT(--(MONTH(B3:B503)=11),--(YEAR(B3:B503)=2007),B3:B503)
Sum Range: '[name.xls]Mid-Term'!$G$3:$G$502

Any further help would be most welcome.





Teethless mama said:
=SUMPRODUCT(--(MONTH(A2:A100)=11),--(YEAR(A2:A100)=2007),B2:B100)
Click to expand...
Click to expand...
 
Top