Double counting in SUMPRODUCT formula

Hi.
There are two questions inherent to this thread. But let's go step-wise:

I have this formula in which I want to sum items (Description) that contains ANY of the strings in the C16 to C17 range:
= SUMPRODUCT( SUMIFS(Value; Description; "*"& C16:C17 &"*") )

This formula however, double counts any rows that contain BOTH strings.
Any idea on how to get around this?

Re: Double counting in SUMPRODUCT formula

Hi Carim. And thanks for the reply.
Your suggested method do eliminate the "double counting". However, it presents another challenge: it only counts the items that contain both strings... ignoring those that contain at least one of them.

Re: Double counting in SUMPRODUCT formula

Exactly.

Re: Double counting in SUMPRODUCT formula

Quote from Carim;788063

Meanwhile for cell E22 ... you could test

Code

=SUMPRODUCT(--NOT(ISNUMBER(SEARCH(C21,$B$6:$B$11))),--NOT(ISNUMBER(SEARCH(C22,$B$6:$B$11)))*(Value))

HTH

For now, let's just focus on adjusting the formula for the first two criteria (C16 and C17) and leave the "exclusions" for later. This exercise is very complicated already.

Quote from Carim;788063

If you do mean OR between all your criteria ... you will just end up ... with the Sum ...

Yes, it will be a sum.
But the idea is more complex than that. That's because the sum has to be based on a range of criteria, in order to count all the items containing any of the strings in the criteria range.

Hope that was clear enough.

Re: Double counting in SUMPRODUCT formula

Those worked, Carim. At least for the current requirements of the spreadsheet.

But as you may have noticed, the table in my spreadsheet is extremely simplified... and so is the criteria part.
Meaning that in the long run, I'll have dozen more criteria ranges to deal with, so the formula has to be as versatile as possible.

So I tried to tweak your formula, in order to accommodate a third criteria ("Description" must contain: "Forklift").
Since the item that contains the word "forklift" does not meet one of the criteria, more specifically the Analytical Center criteria (6512, instead of 6511) the result should stay at the original 2550. But instead, the tweaked formula returns 3650.
In other words, the item containing "forklift" was accepted in the sum... when it shouldn't have been.

Here is the tweaked formula (in red, the only modification made to it):
=SUMPRODUCT((Depr_Account=C19)*(Analytical_C=C18)*--ISNUMBER(SEARCH(C16;Description))+--ISNUMBER(SEARCH(D17;Description))+(--ISNUMBER(SEARCH(C17;Description))*--NOT(ISNUMBER(SEARCH(C16;$B$6:$B$11))));Value)

It seems to be well tweaked.
And if so, why is it returning an incorrect value? (see the attachment)

Re: Double counting in SUMPRODUCT formula

Sorry for the double post.

Carim, I attached the wrong file in the previous post. Please check the below attachment.

Re: Double counting in SUMPRODUCT formula

Dear Carim, I think you misread my post.

The formula works partially. It still doesn't meet the whole requirements of the spreadsheet.