Hi All,

I need help working through the statistics of Google AdWords campaigns. I'm trying to analyse the performance of certain keywords and phrases that are used over multiple campaigns. I've attached an example problem that I made up to help you understand what I mean Example Problem.xlsx. I do not mind if I have to make a template and copy the results (statistics sheet in example) and relevant keywords in to get the final outcome.

Currently I've been using SUMIF functions with wildcards to sort out the performance of keywords but only just realised that it will include valid values (or keywords) that I do not want i.e. Want Clicks of "Luxury..." and not Clicks for "Luxury Garden...". I need to know a way to do a SUMIF and COUNTIF where I can exclude these valid values.

Need to be able to SUMIF phrases that may have a variable in the middle i.e. "phrase X phrase" where X is variable.

Also, I need to calculate the average Cost-Per-Click (CPC) for keywords and phrases without including some valid values (same issue as above), and not dividing by valid keywords that have $0 CPC or could use 0 clicks as the value (in the example I accidentally have keywords with 0 clicks having an avg CPC). There are 2 ways I can potentially do this that I can see, A) with AVERAGEIF formula or B) using COUNTIF and dividing the SUMIF of valid CPC's by a COUNTIF of valid keywords.

I would prefer to make a template where I could literally just copy keywords and the address in and it spits the values out i.e. automatically searches both arrays for valid values and exclusion values/keywords. I'm making a master copy where I'm summing multiple campaigns results to spit out a performance of each keyword under multiple tests.

SUMMARY:
  • Need to Sum multiple values if valid, yet exclude some valid values e.g. Clicks for "Luxury..." but not for "Luxury gardens"
  • Need to Average Cost-per-click (CPC) of valid values yet exclude some valid values (same as above)
  • Need to Average Cost-per-click (CPC) of valid values yet exclude resulting value that is less than 0 i.e. avg = #/Total, exclude count in total of valid values less than 0
  • Need to Sum phrases that have changing variable in the middle e.g."phrase X phrase" where X is a changing variable
  • Would like to make it automatic i.e. searches both array's for valid and exclusion values e.g. searches array one for exclusion values that have keyword in them, then searches array two for valid values and SUMS valid except the exclusion values
  • Can be combined into a master template where I can copy paste values in and it automatically works out values

I know this is hectic, and I'm really hoping excel has the power to do this!

Thank you!