Nested "sumif" statements?

[MN_Excel]

I'm basically trying to do two sumif statements into one formula. If the cell in column B is "sales" I want to add the amount in column C. However, even if the value in column B is "sales" I don't want to add the amount in column C if column A is either "discounts" or "returns." Not sure the easiest way to do this.

 

[Scott Huish]

What version of Excel are you using?

 

[MN_Excel]

2007

 

[snoopyhr]

If the content of column A is not discount or return , is it left blank ?

 

[MrVillareal]

Hi try this,

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="background-color: #FFFF00;;">Amount</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;"></td><td style=";">Sales</td><td style="text-align: right;;">1000</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Return</td><td style="text-align: right;;"></td><td style="text-align: right;;">10</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Discount</td><td style="text-align: right;;"></td><td style="text-align: right;;">20</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;"></td><td style=";">Sales</td><td style="text-align: right;;">505</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;"></td><td style=";">Sales</td><td style="text-align: right;;">124</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;"></td><td style=";">Sales</td><td style="text-align: right;;">152</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">Return</td><td style="text-align: right;;"></td><td style="text-align: right;;">101</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">Discount</td><td style="text-align: right;;"></td><td style="text-align: right;;">202</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="background-color: #FFFF00;;">Total Amount of Sales</td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #FFFF00;;">1781</td></tr></tbody></table><p style="width:4.2em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet26</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C10</th><td style="text-align:left">=SUMIF(<font color="Blue">B1:B9,"Sales",C1:C9</font>)</td></tr></tbody></table></td></tr></table><br />

 

[MN_Excel]

No, there are items in column A besides either "returns" or "discounts" but all other values can be included in my sum.

 

[MN_Excel]

Mr Villareal,

Thanks for the formula, the problem is that where there are blank cells in your example, it is should actually be "sales"

 

[Scott Huish]

=SUMIFS(C:C,A:A,"<>discounts",A:A,"<>returns",B:B,"sales")

 

[Weaver]

I understand the OP has 2007, but just in case anyone with an older version of Excel is looking at this thread for help

=SUMPRODUCT(--(A2:A9<>"Return"),--(A2:A9<>"Discount"),--(B2:B9="Sales"),C2:C9)

 

[snoopyhr]

In case none formula solution needed:

Other alternative would be to utilize pivot table, when you deselect "Discount" and "Return" the resulting table will be all sales other than those two and the total is shown at the bottom. Advantage, if in the future you want to get more info such as date of sales, invoice numbers or any other relative information as a extracted list, you can do so by just adding new field. Disadvantage is, it is not dynamic and need to be refreshed as items added.