Easy Solution to SUMIFS with variable criteria???

[Gladston]

Hello All,

I am looking for an easy solution to a rather tedious formula. Here's the situation... I have a list of data that I would like to SUMIFS. From that SUMIFS, I would like to subtract out values that meet certain criteria in another, separate range. Lets use an example to help better understand what I'm looking for:

Say I have a list of various fruit. Those fruit have respective $$ values in an adjacent column. So I might have Apple worth $5, Pear worth $3, Berries worth $6, Apple worth $2, Watermelon worth $6, and so on. If I want to say, give me the TOTAL SUM of all of fruits value, but subtract out the values of the Apples and the Pears... My first go to equation would be to SUM the entire column showing the $$ values, and the do one SUMIFS to subtract out the Apples, and another SUMIFS to subtract out the Pears. So I'm left with SUMIFS - SUMIFS - SUMIFS. What I am looking for is the following... I want to essentially make a list on another tab that lists the "Forbidden Fruit", where I can put "Apples" and "Pears" and have a simple SUMIFS - ?????. This way, if I ever update the table, add more fruits, or remove some fruits, the formula will automatically update... It will also make the original formula cleaner because I won't have a reducing SUMIFS equation for each "Forbidden Fruit".

My actual data is much more complicated and, at the moment, I have 7 "forbidden Fruits"... so you can see why I am trying to reduce the formula size down... Nonetheless, still looking for this type of easier solution.

Thanks in advance for your assistance!

Brad

 

[Scott Huish]

Assuming your fruit and values in Columns A and B and your forbidden fruit list in Column E:

=SUMPRODUCT(--ISERROR(MATCH(A:A,E:E,0)),B:B)

<b>Sheet1</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:143px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >Fruit </td><td >Value</td><td > </td><td > </td><td >Forbidden Fruit</td><td > </td><td > </td><td >SUM-Forbidden Fruit</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >Apple</td><td style="text-align:right; ">5</td><td > </td><td > </td><td >Apple</td><td > </td><td > </td><td style="text-align:right; ">6</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >Pear</td><td style="text-align:right; ">3</td><td > </td><td > </td><td >Pear</td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >Berries</td><td style="text-align:right; ">6</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >H2</td><td >=SUMPRODUCT(--ISERROR<span style=' color:008000; '>(MATCH<span style=' color:#0000ff; '>(A:A,E:E,0)</span>)</span>,B:B)</td></tr></table></td></tr></table> <br /><br /><span style="font-family:Arial; font-size:9pt; font-weight:bold;background-color:#ffffff; color:#000000; ">Excel tables to the web >> </span>

 

[Jonmo1]

If you reverse the logic..
i.e. Instead of summing the ones that DO NOT match your 'forbidden fruits'..

Sum the ones that DO, and subtract that from the overall SUM..

=SUM(B:B)-SUMPRODUCT(SUMIF(A:A,'Other Tab'!A$1:A$7,B:B))

Where 'Other Tab'!A$1:A$7 is your list of 'forbidden fruits'


And taking it further...
It seems to me as far as information provided by your sheet at the end of the day
That the sum of the NonForbidden fruits would be just as valuable a piece of information, as the sum of Forbidden, and the total sum of all fruits.

So designate 3 cells,
1 for the total sum of all fruits (say E2)
1 for the sum of Forbidden Fruits (Say E3)
1 for the sum of NON forbidden Fruits (Say E4)

E2: =SUM(B:B)
E3: =E2-E4
E4: =SUMPRODUCT(SUMIF(A:A,'Other Tab'!A$1:A$7,B:B))

 

[Jonmo1]

I reversed the descriptions of E3 and E4
E3 would be the Non Forbidden, and E4 is the Forbidden

 

[Gladston]

Assuming your fruit and values in Columns A and B and your forbidden fruit list in Column E:

=SUMPRODUCT(--ISERROR(MATCH(A:A,E:E,0)),B:B)

<b>Sheet1</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:143px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td >*</td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >Fruit </td><td >Value</td><td >*</td><td >*</td><td >Forbidden Fruit</td><td >*</td><td >*</td><td >SUM-Forbidden Fruit</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >Apple</td><td style="text-align:right; ">5</td><td >*</td><td >*</td><td >Apple</td><td >*</td><td >*</td><td style="text-align:right; ">6</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >Pear</td><td style="text-align:right; ">3</td><td >*</td><td >*</td><td >Pear</td><td >*</td><td >*</td><td >*</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >Berries</td><td style="text-align:right; ">6</td><td >*</td><td >*</td><td >*</td><td >*</td><td >*</td><td >*</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >H2</td><td >=SUMPRODUCT(--ISERROR<span style=' color:008000; '>(MATCH<span style=' color:#0000ff; '>(A:A,E:E,0)</span>)</span>,B:B)</td></tr></table></td></tr></table> <br /><br /><span style="font-family:Arial; font-size:9pt; font-weight:bold;background-color:#ffffff; color:#000000; ">Excel tables to the web >> </span>

Awesome thank you so much!!! This seems to be working great. What if I would like to add in another criteria now? So if I wanted to add in date criteria, how would that fit into the formula?

THanks so much!

BRad

 

[Gladston]

Assuming your fruit and values in Columns A and B and your forbidden fruit list in Column E:

=SUMPRODUCT(--ISERROR(MATCH(A:A,E:E,0)),B:B)

<b>Sheet1</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:143px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td >*</td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >Fruit </td><td >Value</td><td >*</td><td >*</td><td >Forbidden Fruit</td><td >*</td><td >*</td><td >SUM-Forbidden Fruit</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >Apple</td><td style="text-align:right; ">5</td><td >*</td><td >*</td><td >Apple</td><td >*</td><td >*</td><td style="text-align:right; ">6</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >Pear</td><td style="text-align:right; ">3</td><td >*</td><td >*</td><td >Pear</td><td >*</td><td >*</td><td >*</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >Berries</td><td style="text-align:right; ">6</td><td >*</td><td >*</td><td >*</td><td >*</td><td >*</td><td >*</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >H2</td><td >=SUMPRODUCT(--ISERROR<span style=' color:008000; '>(MATCH<span style=' color:#0000ff; '>(A:A,E:E,0)</span>)</span>,B:B)</td></tr></table></td></tr></table> <br /><br /><span style="font-family:Arial; font-size:9pt; font-weight:bold;background-color:#ffffff; color:#000000; ">Excel tables to the web >> </span>

So for example, I would like to say SUMPRODUCT based ont he fordbidden items... but sum it only if its going to be between Date A and Date B. This is ultimately what I am looking to do.

THanks!

BRad