SUMIF with Multiple Criteria

[chickalina]

=SUMIF(D33001,"014",
G3:G5000)+SUMIF(I4:I10,"<"&DATE(2009,1,1),G4:G10)-SUMIF(I4:I10,"<"&DATE(2009,1,31),G4:G10)

what's wrong with this code? I'm looking to sum everything for building 14
(located in column D) from January 1 to January 31 (Located in column I).
Column G is where the monetary values are....

Thanks for any help!
M

 

[Bernard Liengme]

You need to use SUMPRODUCT for multiple criteria
=SUMPRODUCT(--(D33001="014"), --(MONTH(I4:I10+=1), G4:G10)
For more details on SUMPRODUCT
Bob Phillips
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
J.E McGimpsey
http://mcgimpsey.com/excel/formulae/doubleneg.html

If you have Excel 2007 you can use SUMIFS
best wishes

 

[Shane Devenshire]

Hi,

Try something like this

=SUMPRODUCT(--(D310="014"),--(I4:I10>=DATE(2009,1,1)),--(I4:I10<=DATE(2009,1,31)),G4:G10)

But note your logic is different from that which I am using above. For
example your first range D33001 does not match your other criteria ranges.
Your formula misses Jan 31.

 

[Shane Devenshire]

Hi,

In 2007

=SUMIFS(G3:G10,D310,"014",I3:I10,">="&DATE(2009,1,1),I3:I10,"<="&DATE(2009,1,31))

In my prevous post I failed to notice that you are running some ranges from
row 3 and others from row 4. All ranges must be of equal size for these
formulas, so you should correct my previous suggestion accordingly.

 

[Bernard Liengme]

A typo!
=SUMPRODUCT(--(D33001="014"), --(MONTH(I4:I10)=1), G4:G10)