Nesting =SUMIF

[smithgt]

Is it possible to use =SUMIF on two criteria being met?

My current formula is

=SUMIF($I:$I,"X",M:M)

but I'd like to add another condition that of

IF I="X" and K="Australia" then SUM the value in M

Thanks

 

[Joe4]

This question was just asked this morning: http://www.mrexcel.com/forum/showthread.php?t=560796

(in fact, I think this may be the most commonly asked question!).

 

[JamesW]

Hi,

Try:

=SUMPRODUCT(--(I1:I1000="X"),(K1:K1000="Australia")*M1:M1000)

 

[smithgt]

Hi,

Try:

=SUMPRODUCT(--(I1:I1000="X"),(K1:K1000="Australia")*M1:M1000)

This just gives me the #VALUE! error message, presumably because its trying to multiply a value by a text field.

 

[JamesW]

I'm not sure I understand.

What do you mean by mutliplying a value by a text field?

You are only summing up the numbers in column M.

I have tested the formula myself and it works fine - (Column I contained X, Y, Z. Column K contained Australia, England, Germany. Column M contained random numbers).

<table style="width: 229px;" border="0" cellpadding="0" cellspacing="0"><col style="width: 33pt;" width="44"> <col style="width: 11pt;" width="15"> <col style="width: 45pt;" width="60"> <col style="width: 11pt;" width="14"> <col style="width: 43pt;" width="57"> <col style="width: 29pt;" width="39"> <tbody><tr style="height: 12.75pt;" height="17"><td class="xl24" style="height: 12.75pt; width: 33pt;" width="44" height="17">Letter</td><td class="xl24" style="width: 11pt;" width="15">
</td><td class="xl24" style="width: 45pt;" width="60">Country</td><td class="xl24" style="width: 11pt;" width="14">
</td><td class="xl24" style="width: 43pt;" width="57">Number</td><td class="xl24" style="width: 29pt;" width="39">Total</td></tr><tr style="height: 12.75pt;" height="17"><td style="height: 12.75pt;" height="17">X</td><td>
</td><td>Australia</td><td>
</td><td align="right">123</td><td align="right">941</td></tr><tr style="height: 12.75pt;" height="17"><td style="height: 12.75pt;" height="17">X</td><td>
</td><td>Australia</td><td>
</td><td align="right">4</td><td>
</td></tr><tr style="height: 12.75pt;" height="17"><td style="height: 12.75pt;" height="17">X</td><td>
</td><td>Australia</td><td>
</td><td align="right">125</td><td>
</td></tr><tr style="height: 12.75pt;" height="17"><td style="height: 12.75pt;" height="17">Y</td><td>
</td><td>Australia</td><td>
</td><td align="right">15</td><td>
</td></tr><tr style="height: 12.75pt;" height="17"><td style="height: 12.75pt;" height="17">Y</td><td>
</td><td>England</td><td>
</td><td align="right">661</td><td>
</td></tr><tr style="height: 12.75pt;" height="17"><td style="height: 12.75pt;" height="17">Y</td><td>
</td><td>England</td><td>
</td><td align="right">1</td><td>
</td></tr><tr style="height: 12.75pt;" height="17"><td style="height: 12.75pt;" height="17">Y</td><td>
</td><td>Germany</td><td>
</td><td align="right">2</td><td>
</td></tr><tr style="height: 12.75pt;" height="17"><td style="height: 12.75pt;" height="17">Z</td><td>
</td><td>Germany</td><td>
</td><td align="right">6</td><td>
</td></tr><tr style="height: 12.75pt;" height="17"><td style="height: 12.75pt;" height="17">Z</td><td>
</td><td>Germany</td><td>
</td><td align="right">6</td><td>
</td></tr><tr style="height: 12.75pt;" height="17"><td style="height: 12.75pt;" height="17">Z</td><td>
</td><td>Germany</td><td>
</td><td align="right">7</td><td>
</td></tr><tr style="height: 12.75pt;" height="17"><td style="height: 12.75pt;" height="17">Z</td><td>
</td><td>Germany</td><td>
</td><td align="right">32</td><td>
</td></tr><tr style="height: 12.75pt;" height="17"><td style="height: 12.75pt;" height="17">Z</td><td>
</td><td>Germany</td><td>
</td><td align="right">3</td><td>
</td></tr><tr style="height: 12.75pt;" height="17"><td style="height: 12.75pt;" height="17">X</td><td>
</td><td>Germany</td><td>
</td><td align="right">26</td><td>
</td></tr><tr style="height: 12.75pt;" height="17"><td style="height: 12.75pt;" height="17">X</td><td>
</td><td>Germany</td><td>
</td><td align="right">1</td><td>
</td></tr><tr style="height: 12.75pt;" height="17"><td style="height: 12.75pt;" height="17">X</td><td>
</td><td>England</td><td>
</td><td align="right">2</td><td>
</td></tr><tr style="height: 12.75pt;" height="17"><td style="height: 12.75pt;" height="17">X</td><td>
</td><td>Australia</td><td>
</td><td align="right">23</td><td>
</td></tr><tr style="height: 12.75pt;" height="17"><td style="height: 12.75pt;" height="17">X</td><td>
</td><td>Australia</td><td>
</td><td align="right">666</td><td>
</td></tr></tbody></table>

 

[Joe4]

Are you using Excel 2007 or later? If so, you can use the SUMIFS function, which allows multiple criteria.

If not, look at the thread I linked, specifically my post and link to the explanation on how the SUMPRODUCT function works.

 

[smithgt]

Thanks All,

I resolved my #Values error by adjusting the range to start at row 3. The top two rows coantained labels.

 

[Aladin Akyurek]

Thanks All,

I resolved my #Values error by adjusting the range to start at row 3. The top two rows coantained labels.

What is the exact formula that you have implemented?