Sum a column that meets two criteria

[terrapinie]

I need to sum a column of numbers if it matches two different criteria.
I can set up the SUMIF easily for meeting one criteria, but I need to
also sum the column if it meets that criteria, and another. For
example:
A B C
1 150 ABC MS1
2 200 DEF MS0
3 100 LMN MS0
4 125 ABC MS1
5 175 LMN MS1
6 225 DEF MS0

I need to have a formula that would say <<Sum column A IF column B =
"DEF" AND column C = "MS0">>. (and so forth for the other
combinations).
I know there has to be a way to do this, probably using a combination
of an IF and SUMIF functions - but i keep getting #VALUE for answers.

Any help will be GREATLY appreciated.

Thanks so much -
Laurie

 

[Domenic]

Try...

=SUMPRODUCT(--(B1:B6="DEF"),--(C1:C6="MSO"),A1:A6)

OR

=SUMPRODUCT(--(B1:B6=D1),--(C1:C6=E1),A1:A6)

....where D1 contains your first criterion, and E1 your second.

Hope this helps!

 

[Chris Ferguson]

Hi

Have you looked at DSUM ?

You need column headers and a 2 line criteria area.
I added a row 1 and user amount, crit1 and crit2 as headers and then set a
criteria area in f1 to g2
f1 = crit1
g1 = g2
f2 = 'def'
g2 = 'ms0'

Then in any cell =dsum(a1:c6,a1,f1:g2) and got the answer 200.
Changing the criteria in f2 and g2 gives the different totals or you could
have several criterias=, one for each total you require.

Regards

Chris

 

[CLR]

Take a look at the Data > Subtotals function..........

Using a helper column, CONCATENATE the two criteria columns , now you have
one column combining both criteria........and do your subtotal separtation
on that column and SUM the Amount column

hth
Vaya con Dios,
Chuck, CABGx3

 

[terrapinie]

Thanks so much for the speedy responses!!! I tried the SUMPRODUCT and
it works like a charm!!!

Thanks again!!
Laurie

 

[terrapinie]

Thanks so much for the speedy responses!!! I tried the SUMPRODUCT and
it works like a charm!!!

Thanks again!!
Laurie