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

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

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" <brentm@discussions.microsoft.com> wrote in message
news:2AD46D45-6D8C-41A8-BCEE-122A672375B7@microsoft.com...
> 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

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" <brentm@discussions.microsoft.com> wrote in message
news:2AD46D45-6D8C-41A8-BCEE-122A672375B7@microsoft.com...
> 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

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

Another approach would be to create a column that hold a concatentation of other columns.

=concatentate(a2, b2, c2)

(note: could also write this as =a2&b2&c2

then, do the sumif based on the concatentated result.

The method i'm using to deal with this kind of situation (conditional summing across columns) is the following:

You should assign a Range name to each column where you want to check for conditions.
E.g. if Column B is 'Month', then assign a Range name to your data (Insert > Name > Define).
Say you named your data in Column A as CODE, Column B as MONTH, and Column C as AMOUNT.

Then the following array function (entered with Ctrl+Shift+Enter) will do the trick:
= Sum ( If ( CODE="TheRightCode" ; If ( MONTH="Jan-04" ; AMOUNT )))

This would sum all Amounts for Jan-04 for your "RightCode", and i find the method to be quite intuitive.
Be careful with dates when doing this, you should convert them to text first (use Excel's TEXT function).
Important: all your named Ranges (Columns) should have the same amount of rows (cells). E.g. if MONTH is A1:A1000, then the other range names should also be from cell 1:1000 in their respective columns.
Tip: you can use dynamic ranges (using the Offset function) so you don't have to worry about how long your data list will get.

If you can get back with a more specific example (your exact Field Names and formats of your columns and the exact summing you wish to perform) we could get more specific.


Hope this is of help,
Alex