SUMIF (Excel '97)

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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,
 
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.
 
=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)
 
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
 
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.
 
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,
 
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...
 
Back
Top