SUMIF with multiple criteria

[Guest]

Ok, here's one for the advanced users.

I have a sheet that is being totalled by month with the following catagories:
I have to sum a column if multiple criteria are met. First, I need to take
into account the status (awarded, submitted, lost, etc.), then the sum must
look to see if those amounts are within a certain month. any ideas on using
multiple criteria within a sumif statement?

Brent M

 

[Jason Morin]

You need to use an array formula that incorportes SUM and
IF (but is not the same as SUMIF) or use SUMPRODUCT. Based
on what you've posted this should start you in the right
direction:

A1:A100 - dates
B1:B100 - status
C1:C100 - values to sum

To sum all "lost" values for the month of April (assuming
year doesn't matter), try:

=SUMPRODUCT((TEXT(A1:A100,"mmm")="Apr")*(B1:B100="lost")
*C1:C100)

To learn more, check out Bob Phillips' website:

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

HTH
Jason
Atlanta, Ga

 

[CLR]

I do similar things with the AutoFilter.......then copying the filtered data
over to a ReportSheet, and then doing SubTotals on that sheet............

hth
Vaya con Dios,
Chuck, CABGx3

 

[Tom Ogilvy]

You might also look at Data=>PivotTable Report.
You might look at Jon Peltier's site.
http://peltiertech.com/Excel/Pivots/pivotstart.htm

 

[KRCowen]

Have you tried an array entered sum formula with multiple criteria?