SUMIF with multiple criteria
 

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

SUMIF with multiple criteria
 

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

-----Original Message-----
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
.

SUMIF with multiple criteria
 

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


"brentm" wrote in message
...
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

SUMIF with multiple criteria
 

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


--
Regards,
Tom Ogilvy

"brentm" wrote in message
...
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

SUMIF with multiple criteria
 

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