# 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?

## Files

• Re: Double counting in SUMPRODUCT formula

Hello,

With your own array formula ... (use Control+Shift+Enter ... instead of Enter )

Code
``= SUMIFS(Value, Description, "*"& C16:C17 &"*")``

Hope this will help

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner

• 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

You are right ... Probably because I read your title ... :wink:

Do you mean all your criteria are independent ... and you need OR ... instead of AND ... ???

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner

• Re: Double counting in SUMPRODUCT formula

Exactly.

• Re: Double counting in SUMPRODUCT formula

Quote from Luis Ah-Hoy Jr.;788062

Exactly.

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

Think you should clarify exactly the relationship between your criteria ...

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

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner

• 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

See attached ...

HTH

## Files

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner

• 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)

## Files

• 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.

## Files

• Re: Double counting in SUMPRODUCT formula

Quote from Luis Ah-Hoy Jr.;788098

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

You are welcome

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner

• 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.

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!