+ Reply to Thread
Results 1 to 2 of 2

Formula to pull Top 10 Keywords based on values in a separate column

  1. #1
    Registered User
    Join Date
    09-11-2017
    Location
    New York
    MS-Off Ver
    2010
    Posts
    1

    Formula to pull Top 10 Keywords based on values in a separate column

    Hi everyone,

    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:
    excelexample1.JPG

    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:
    excelexample2.JPG

    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.

    Thanks!

  2. #2
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    14,907

    Re: Formula to pull Top 10 Keywords based on values in a separate column

    Hello dyljenx and Welcome to Excel Forum.
    It would help us to attempt to help you if you could upload a sample of the raw data table and the results that you would expect. You'll need to manually input results so that contributors can attempt to write formula/code that will replicate those results.
    To upload a sample workbook click on the GO ADVANCED button below the Quick Reply window and then scroll down to Manage Attachments to open the upload window. Make sure there is enough data to demonstrate your need. Remember to desensitize the data if necessary.
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Assign values to categories of values based on separate column attribute.
    By jenbot83 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-06-2017, 02:17 PM
  2. [SOLVED] Make Column that Finds Unique Values Based on Keywords without Duplicates
    By daveys93 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-26-2016, 12:33 PM
  3. [SOLVED] Excel2010 IF Formula to return value based on a separate date and two separate text values
    By jakecutler in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-12-2015, 03:24 AM
  4. Replies: 3
    Last Post: 07-28-2014, 08:00 PM
  5. [SOLVED] Pull Values based on one column
    By bjnockle in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-30-2013, 03:16 AM
  6. [SOLVED] HDI - Return multiple keywords in a text column based on a set of available keywords
    By zneiley in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-25-2013, 01:32 PM
  7. Pull unique values based on adjacent column
    By freud1 in forum Excel General
    Replies: 9
    Last Post: 07-02-2012, 11:36 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1