sumifs using list of cells as critera

[joshman108]

As opposed to doing something like this:

<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 14.0px 'Helvetica Neue'; color: #323333 ; -webkit-text-stroke: #323333 }span.s1 {font-kerning: none}</style>=sumifs(C:C,B:B,"<>fbk",B:B,"<>ofb")

I would like to basically do something like this:

=sumifs(C:C,B:B,<>d1:d4)

where d1:d4 holds a list of things I want to exclude.

Is this possible at all? Haven't found an answer online. Thanks
<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 14.0px 'Helvetica Neue'; color: #323333 ; -webkit-text-stroke: #323333 }span.s1 {font-kerning: none}</style>

 

[Vinoth_Shob]

Just update as 0 in E1 to e14 and then update formula as sumifs(C:C,E:E,<>0)

 

[FormR]

Hi, here is another option you can try, note - you should avoid using full column references with this type of formula.

=SUMPRODUCT(1-ISNUMBER(MATCH(B2:B100,D1:D4,0)),C2:C100)

 

[Jonmo1]

Try

=SUM(C:C)-SUMPRODUCT(SUMIF(B:B,D1:D4,C:C))

 

[joshman108]

FormR, that works beautifully. I love it.

Jonmo1, that works as well, but can you explain how SUMIF(B:B,D1:D4,C:C) returns the proper arguments for sumproduct? I don't understand how that works as sumproduct takes 2 range arguments.

 

[Jonmo1]

as sumproduct takes 2 range arguments.

That's an incorrect assumption.

You can certainly enter only 1 range argument in sumproduct, like =SUMPRODUCT(A1:A10), or even just SUMPRODUCT(A1)
However, it will only behave as a SUM, there will be no 'Product' function occuring.

So the sumif creates an array of of results, 1 for each cell in D1:D4
Like {SUMIF(B:B,D1,C:C),SUMIF(B:B,D2,C:C),SUMIF(B:B,D3,C:C),SUMIF(B:B,D4,C:C)}

Then sumproduct sums that array.

 

[joshman108]

Wow, thanks for explaining. So is creating an array inherent to sumif then? Sumifs does not do the same thing does it?

 

[joshman108]

And why does
SUM(SUMIF(B:B,D1:D4,C:C))

not work? Does SUM simply not work over an array?

 

[Jonmo1]

That way would really only do the D1 value.
You need SUMPRODUCT to force it to process the Array of results.
OR you could use SUM and enter it as an array by pressing CTRL+SHIFT+ENTER

 

[Jonmo1]

Sumifs does not do the same thing does it?

It will work for sumifS as well.
I only changed it to sumif because sumifS was not required (there is only 1 criteria range)