# Need SUMPRODUCT/ SUMIF formula to sum each cell once not dupe if text contains 2 criteria

1. ## Need SUMPRODUCT/ SUMIF formula to sum each cell once not dupe if text contains 2 criteria

Hi,

I have created a SUMPRODUCT/ SUMIF formula with multiple criteria to return the number of impressions that correspond with adverts by a specific competitor which contain any of a range of text.

=SUMPRODUCT(SUMIFS(Raw!F:F, Raw!A:A,"*"&A2&"*",Raw!\$B:\$B,"*"&Themes!\$A\$2:\$A\$28&"*"))/SUMIF( Raw!A:A,"*"&A2&"*", Raw!F:F)

HOWEVER, this formula is counting impressions twice if two of the range of text are within 1 ad text cell. I need the formula to count the impression associated with the ad once if any of the range of texts are contained in it.

Example:

Impressions: 3000

Returns: 6000 when I would need it to return 3000.

I have tried translating to a ISNUMBER(SEARCH formula but failed so far - It must not be case sensitive either.

Any help appreciated!

2. ## Re: Need SUMPRODUCT/ SUMIF formula to sum each cell once not dupe if text contains 2 crite

Hi,

You can use:
=SUMPRODUCT(RAW!F:F,(ISNUMBER(SEARCH(A2,RAW!A:A)))*(COUNT(SEARCH(Themes!\$A\$2:\$A\$28,RAW!\$B:\$B))>0))/SUMIF( RAW!A:A,"*"&A2&"*", RAW!F:F)

but I recommend you avoid referring to entire columns in such a formula, especially so if you repeat the formula a lot.

3. ## Re: Need SUMPRODUCT/ SUMIF formula to sum each cell once not dupe if text contains 2 crite

Hi,

Thanks for the reply but this is only returning 0 now which is incorrect.

I have attached a sample data to show this.

Thanks!

4. ## Re: Need SUMPRODUCT/ SUMIF formula to sum each cell once not dupe if text contains 2 crite

You need to enter the formulae with Ctrl+Shift+Enter

plus your formula in C20 refers to B20 not A20

5. ## Re: Need SUMPRODUCT/ SUMIF formula to sum each cell once not dupe if text contains 2 crite

Originally Posted by JohnTopley
You need to enter the formulae with Ctrl+Shift+Enter
Thank you. Yes, I should have made that clear. Additionally, there is no need for SUMPRODUCT when SUM will achieve the same result.

6. ## Re: Need SUMPRODUCT/ SUMIF formula to sum each cell once not dupe if text contains 2 crite

Thanks a lot for your help! It is working with SUMPRODUCT now but SUM does give an inflated number.

7. ## Re: Need SUMPRODUCT/ SUMIF formula to sum each cell once not dupe if text contains 2 crite

Apologies, I was thinking of something else. You do indeed require SUMPRODUCT for this.

8. ## Re: Need SUMPRODUCT/ SUMIF formula to sum each cell once not dupe if text contains 2 crite

Hi,

Apologies for opening this again but I am struggling to apply this formula to my original data. the formula is just counting all impressions where the first SEARCH criteria is true but ignoring the second.

I have attached a small set, if anyone could help me understand why this is not working please?

Thanks.

9. ## Re: Need SUMPRODUCT/ SUMIF formula to sum each cell once not dupe if text contains 2 crite

It is I who must apologise I'm afraid. The formula I suggested does not work for this at all. I will do my best to rectify the situation.

10. ## Re: Need SUMPRODUCT/ SUMIF formula to sum each cell once not dupe if text contains 2 crite

Perhaps you might be able to use a DSUM solution? I have attached an example workbook.

There are currently 1 users browsing this thread. (0 members and 1 guests)