Home » Forums » Open Source Learning Center » Question and Answer

Returning a sum of values based on data containing a couple of keywords

Submitted by cslunn on 15 January, 2013 - 16:02

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

‹ Please Help Me!!! Anyone. I need a Title Generator - I have description and examples - I lack the knowledge - Any one Help?? bankroll management spreadsheet ›
»

RE: Returning a sum of values...

Submitted by manny on 15 January, 2013 - 18:37.

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.

»