Using VLookup to Find phrase within 1st column and then getting sum from 2nd column

  • Creating Summary Table (Summary) from Pivot Table (Segment Master).
    Within Pivot Table, Column 1 has lots of variable content based on ad sizes plus descriptions (example: 160x600 Air)
    Column 2 has values of impressions.


    I'm trying to fill in column 2 of the Summary Table (Summary), with the sum of the impressions (column 2) that have a common phrase (example: 160x600 Air) in the ad size and description column (column 1) from the Pivot Table (Segment Master).
    The names vary significantly within Column 1 of the Pivot Table so I've tried to use quotations or asterisks.


    Am I trying to do too much?


    1. Identify range within Column 1 of Pivot Table (Segment Master): A6:A503.
    2. Identify where data is being pulled from: Segment Master
    3. Identify what to characters/phrase to look for within column 1 of pivot table: 160x600 AIR
    4. Identify sum of values that are in column 2 of Pivot Table (Segment Master) that meet criteria.
    5. Place sum in Summary Table (Summary) B4.

  • Re: Using VLookup to Find phrase within 1st column and then getting sum from 2nd colu


    You can use wildcard characters in a SUMIF


    eg in cell d1 contain *160x600 AIR* then the formula


    =SUMIF(A:A,D1,B:B)


    will add up all the values in B that have the text 160x600 AIR in column A


    if cell D1 has 160x600 AIR then use


    =SUMIF(A:A,"*"&D1&"*",B:B)

    There are three types of people in this world.
    Those who can count and those who can't.

  • Re: Using VLookup to Find phrase within 1st column and then getting sum from 2nd colu


    Thanks for the response. I'm not sure I was 100% clear in my post as I'm not following the D1 reference.


    The ad sizes and descriptions are located in Column 1 (A) - Range A6:A503. The impression counts (values) are in column 2 (B) - Range B6:B503. I'm wanting to put the sum of these into a separate worksheet in the same file. The summary worksheet is titled "Summary" with the Pivot Table in a separate worksheet titled "Segment Master".


    =SUMIF(A6:A503, "160x600 AIR", "Segment Master", B6:B503) - something like that?

  • Re: Using VLookup to Find phrase within 1st column and then getting sum from 2nd colu


    Sorry if the two columns are in different sheets there's no real solution


    You need the two columns to be in the same sheet (copy + paste) and asterixes around the text
    eg
    =SUMIF(Sheet1!A6:A503, "*160x600 AIR*",Sheet1!B6:B503)


    instead of having the "*160x600 AIR*" in the formula you would typically have *160x600 AIR* in a cell (say D1) and use the cell reference in the formula


    =SUMIF(Sheet1!A6:A503,D1,Sheet1!B6:B503)

    There are three types of people in this world.
    Those who can count and those who can't.

  • Re: Using VLookup to Find phrase within 1st column and then getting sum from 2nd colu


    If Neal's solution is not what you need, then . . .


    To get the most precise answers, it is best to upload a sample workbook (sensitive data scrubbed/removed) that shows a few manually created examples of the desired results.
    The structure and data types of the workbook must exactly match that of the real workbook. Include in the workbook a clear and explicit explanation of all requirements.The sample workbook only needs to contain enough data to illustrate the need to aid with developing the solution.


    [COLOR="navy"]How to edit your post or thread title or upload an attachment[/COLOR]


    1. Click the EDIT POST in the gray band immediately below your post
    2. Click Go Advanced
    3. Edit the post or thread title
    4. To upload: scroll down to Manage Attachments -- use ZIP compression if necessary to meet file size limitations