SUM(IF) cell with multiple descriptors double counting the condition

[inpixels]

Long time listener, first time caller

I am trying to sum cells B1 - B5
but if the description of in cell A meet certain criteria I would like to exclude the corresponding number in cell B

Below I am able to remove references to RUN and BOX.
However if A cell description happens to have *BOTH* RUN & BOX in the description...it is double counting that reference.


=SUM(B1:B5)-SUM(SUMIF(A1:A5,{"*BOX*";"*RUN*"},B1:B5))


A | B
BOX 1
BOX and RUN 5
RUN 5
SHOOT 4
BOX and TROT 2

Total = 17

Removing Box & Run Desired = 17 -(1+5+5+2) = 4 (i.e. this is the result i want)

Removing Box & Run based on my formula = 17 - (1+5+5+5+2) = -1 (unfortunately this is the number i'm getting)

I suppose I need to use an OR argument of some sort or possibly a sumproduct.

thanks for your help.

 

[Matty]

Hi,

I've been playing about with this, but can't get it to work without the user of a helper Column (though I'm sure one of the real experts on here will waltz up shortly with a cool way of how to do it...).

Based on your data being in A1:B5, try:

=IF(COUNT(SEARCH({"BOX","RUN"},A1)),"Yes","No")

In C1 and copy down. Now, simply use:

=SUMIF(C1:C5,"No",B1:B5)

Matty

 

[inpixels]

Thanks.
That is clever and it works

i'm using your formula with (divided by) the following formula to get an average.
=COUNT(B1:b5)-(COUNTIF(C1:5,"=YES"))

I suppose it would be nice to tighten it up as an all in one formula but it works great with the "helper" Y/N column ...

Cheers!

 

[Matty]

Thanks.
That is clever and it works

i'm using your formula with (divided by) the following formula to get an average.
=COUNT(B1:b5)-(COUNTIF(C1:5,"=YES"))

I suppose it would be nice to tighten it up as an all in one formula but it works great with the "helper" Y/N column ...

Cheers!

Glad it worked.

For the average, you could just use:

=SUMIF(C1:C5,"No",B1:B5)/COUNTIF(C1:C5,"No")

I'm still intrigued to know whether there is an 'all in one' formula that will do this though, i.e. without the helper Column. Perhaps my response here will prompt others to look at this thread again.

Matty