Returning a sum of values based on data containing a couple of keywords
Hi, I have exported a load of keyword data from Google Analytics so I have a few key columns; 1/ Keyword and 2/ Visits.
The keyword column contains numerous variations of keywords and I'd like a formula which helps me count the number of total visits by any phrase in the list which contains 2 or more keywords.
For example:
Column A Column B
Bracknell Office Services 50
Bracknell Office 50
Bracknell Services 50
Office Services Bracknell 50
If I wanted to get a formula to count the values in column B every time the word 'Bracknell' and 'Office' appeared together, how would I write this?
The answer should be 150 based on the above example as the word Office doesn't actually appear in the 3rd line so shouldn't be counted.
Thanks in advance for any support on this...
RE: Returning a sum of values...
Hi,
Here's some examples:
- for Excel 2007-2010:
= SUMIFS(B1:B4, A1:A4, "*Bracknell*", A1:A4, "*Office*")
- for Excel 2003, if for some reason you still use it:
Note: The following formulas are array formula. Press CTRL + SHIFT + ENTER after type the formula instead of only ENTER. For this particular example both formulas are identically.
= SUM(IF((IF(ISERROR(FIND("Bracknell", A1:A4)), 0, FIND("Bracknell", A1:A4)) > 0) * (IF(ISERROR(FIND("Office", A1:A4)), 0, FIND("Office", A1:A4)) > 0), B1:B4, 0))
or
= SUMPRODUCT(--(IF(ISERROR(FIND("Bracknell", A1:A4)), 0, FIND("Bracknell", A1:A4)) > 0), --(IF(ISERROR(FIND("Office", A1:A4)), 0, FIND("Office", A1:A4)) > 0), B1:B4)
Best regards.