Sum a column that meets two criteria

  • Thread starter Thread starter terrapinie
  • Start date Start date
T

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
 
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!
 
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
 
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
 
Thanks so much for the speedy responses!!! I tried the SUMPRODUCT and
it works like a charm!!!

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

Thanks again!!
Laurie
 
Back
Top