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.

 

[David Biddulph]

I'm a bit confused as to what you are trying to do. You seem to be testing
column I dates in rows 4 to 10, but column D values in rows 3 to 3001.

Your 2nd and 3rd terms will subtract those values beween G4 and G10 which
correspond to column I dates from 1 to 30 January 2009 (not 31st, because
you've tested for *less than* 31st), but your first term isn't valid because
the ranges are of different lengths (to 3001 in D, but to row 5000 in G).

If you correct the array lengths in the first part, that will add those
values in G3 to G3001 (or to G5000) for which the corresponding column D
value is "014". After that your 2nd and 3rd terms will subtract the
selected (1 to 30 January 2009) values within your shorter range (rows 4 to
10).

If all your ranges were the same length and you were looking at rows where
columns D and I both met the specified criteria, you would use SUMPRODUCT.

 

[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)