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:
Advertiser: Adidas
Ad: Buy online today
Impressions: 3000
Range of 'Themes': Buy, Today
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!
Bookmarks