Sum a column that meets two criteria

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 this:

{=SUM(IF($B$6:$B$11="abc",IF($C$6:$C$11="ms1",$A$6:$A$11,0),0))}

The result using your data would be 275

A B C
150 abc ms1
200 def MS0
100 LMN MS0
125 ABC MS1
175 LMN MS1
225 DEF MS0


You can write the formula easily using the conditional sum wizard.

Hope this helps

Cheers,

Steve

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!

In article <[email protected]>,
"terrapinie" <[email protected]> wrote:

> 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

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



"terrapinie" <[email protected]> wrote in message
news:[email protected]...
>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
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>

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