SUMIF based on two conditions

[grey]

I have a list of data, which goes something like this

Month Type Amount
Jan A 10
Jan A 15
Feb B 10
Jan B 5
March A 20

etc

Now, I have a second sheet, which I want to go something like this..

Jan

Type A TOTAL
Type B TOTAL

Feb

Type A TOTAL
Type B TOTAL

Mar

Type A TOTAL
Type B TOTAL

In order to do this I need to have a formula which says

"SUMIF (Month=Jan and Type=A)"

Is there anyway to do this?

 

[dominicb]

Good afternoon Grey

There is a way to do this but we'll have to utilise arrays to do so.
The example below assumes that your data containing conditions is in
column A and B and your data to sum is in column C. My assumtion is
that your data covers 50 rows.

=SUM(IF($A$1:$A$50="January",IF($B$1:$B$50="a",$C$1:$C$50,0),0))

As it's an array formula don't forget to use Ctrl + Shift + Enter to
commit it (not just Enter on it's own).

HTH

DominicB

 

[Bob Phillips]

=SUMPRODUCT(--($A$2:$A$100="Jan"),--("$B$2:$B$100="A"),$C$2:$C$100)

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Andrew L via OfficeKB.com]

Yes. Use an "array" formula.

A B C

1 | Month Type Amount
2 | Jan A 10
3 | Jan A 15
4 | Feb B 10
5 | Jan B 5
6 | Mar A 20

Jan
Type A 25 The formula is: =SUM(IF((B2:B6="A")*(A2:A6="JAN"),C2:C6))
Type B 5 The formula is: =SUM(IF((B2:B6="B")*(A2:A6="JAN"),C2:C6))

This is an example of an "array" formula.
To create the array, you enter the formula as above and then
hold down the CTRL and SHIFT keys as you press the ENTER key.

There is a nice tutorial on array functions at:
http://www.cpearson.com/excel/array.htm

Regards,
Andrew

 

[Andrew L via OfficeKB.com]

Of course the example I gave showed the return area on the same page as the
data, but there is no reason you can't do the same think on a different page.
The formula would just contain the sheet reference. It would look something
like:
{=SUM(IF((SheetData!B2:B6="A")*(SheetData!A2:A6="JAN"),SheetData!C2:C6))}

 

[Guest]

Maybe a pivot table would be faster & easier

 

[Anne Troy]

Hi, grey. I would probably use Subtotals instead:
http://www.officearticles.com/excel/getting_subtotals_in_microsoft_excel.htm

But to answer your question, try the instructions here:
http://www.officearticles.com/excel/sumif_using_multiple_criteria_in_microsoft_excel.htm

*******************
~Anne Troy

www.OfficeArticles.com