I'm trying to work on a table for Search Engine Marketing that would give me the top keyword based on a value in a different column. One of the issues I've had with the index/match/large functions is that I have daily data ranging all of 2017 so far and it will return the keyword based on the highest single instance of click volume through the entire year; I'd like for it to see every instance of the keyword within a set period (aka a Month) and determine the highest aggregate click volume and return that keyword...I hope this make sense, I'll break it out into a simpler table version of what I'm working with:
Basic version of the table I'm trying to build, Column A is where I need a formula to extract a keyword based on its aggregate click volume within a single month, Columns B & C use sumifs to pull the impressions and click data from the keyword in column A based on a defined month:
All of this data is being pulled from a raw data set turned into dynamic table, so I can pull metrics based on labels instead of cell ranges (i.e. if I want to sum all clicks =sum(RawData[Clicks])).
So what I'm looking for is a formula that will go into the raw data table, sum the clicks from column C per keywords in column A and Month in column D, then return the keyword from column A of the raw data that has the highest summed clicks within the Month defined in column D. Below is an example of the column headers for my raw data:
Am I getting too complicated with this? It seems like a simple enough idea in my head, but I'm not sure if I can accomplish this with my current data set up and a single formula.