Results 1 to 8 of 8

Thread: VLOOKUP help (not SUMIF)

  1. #1
    New Member
    Join Date
    Aug 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Post VLOOKUP help (not SUMIF)

    I've seen a few of the forums in here with similar requests - how to take a column of data and where there's matches, total the sum of those cells. The consensus seems to be to use SUMIF instead, but I do think I need to use VLOOKUP.

    My goal is to analyze the data, and output a tallied set of results. Here's an example:
    A B C
    Person 1 $500 Product 1
    Person 2 $500 Product 2
    Person 3 $500 Product 3
    Person 1 $500 Product 2
    Person 3 $500 Product 2
    Person 1 $500 Product 1


    I have hundreds of people in column A, Column B is the total value I'd like to sum up, and Column C is the various Products. I have about 10 products that feature.

    I'd like to use a formula that then takes the table data and outputs a summary (by total of column B, per person)

    A B
    Person 1 $1500
    Person 2 $500
    Person 3 $1000

    And, I'd also like to have a formula that takes the table data and outputs a summary (by total of Product and its summed total of value)

    Appreciate any help you can provide!

    Thanks.

  2. #2
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,827
    Post Thanks / Like
    Mentioned
    32 Post(s)
    Tagged
    1 Thread(s)

    Cool Re: VLOOKUP help (not SUMIF)

    maybe use PivotTable

    Person Value Product Person Sum of Value Product Sum of Value
    Person 1
    500
    Product 1 Person 1
    1500
    Product 1
    1000
    Person 2
    500
    Product 2 Person 2
    500
    Product 2
    1500
    Person 3
    500
    Product 3 Person 3
    1000
    Product 3
    500
    Person 1
    500
    Product 2
    Person 3
    500
    Product 2
    Person 1
    500
    Product 1
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

  3. #3
    Board Regular
    Join Date
    Aug 2014
    Posts
    54
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VLOOKUP help (not SUMIF)

    =sumproduct(--(A2:A900=Person1)*(C2:C900=Product1)*B2)

    Unverified this works, but sumproduct is what you want

  4. #4
    Board Regular MARK858's Avatar
    Join Date
    Nov 2010
    Location
    Southern England
    Posts
    11,101
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    2 Thread(s)

    Default Re: VLOOKUP help (not SUMIF)

    Why not SumIf and SumIfs if you want a combination of Person/Product?

    ABCDEFGHIJK
    1Person 1$200Product 1Person 11200Product 1700Person 3 / Product 2100
    2Person 2$300Product 2Person 2300Product 2900Person 1/ Product 1700
    3Person 3$700Product 3Person 3800Product 3700
    4Person 1$500Product 2
    5Person 3$100Product 2
    6Person 1$500Product 1

    Sheet2



    Worksheet Formulas
    CellFormula
    K1=SUMIFS($B$1:$B$6,$A$1:$A$6,E3,$C1:$C6,G2)
    K2=SUMIFS($B$1:$B$6,$A$1:$A$6,E1,$C$1:$C$6,G1)
    F1=SUMIF($A$1:$A$6,E1,$B$1:$B$6)
    F2=SUMIF($A$1:$A$6,E2,$B$1:$B$6)
    F3=SUMIF($A$1:$A$6,E3,$B$1:$B$6)
    H1=SUMIF($C$1:$C$6,G1,$B$1:$B$6)
    H2=SUMIF($C$1:$C$6,G2,$B$1:$B$6)
    H3=SUMIF($C$1:$C$6,G3,$B$1:$B$6)

    Last edited by MARK858; Aug 22nd, 2019 at 03:00 AM.
    Test VBA on a copy of your data (remember you can't normally reverse the action)

    Please follow the forum Rules and Guidelines & please use Code tags around your code i.e. [CODE]your code[/CODE]

    To post a screenshot try one of these links
    MrExcel HTML Maker, RoryA addin (Win & Mac) or Borders-Copy-Paste

  5. #5
    New Member
    Join Date
    Aug 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VLOOKUP help (not SUMIF)

    challenge here is there is a pretty long list of "persons" and I don't want to have to manually type the formula in to make sure I get everyone. I understood VLOOKUP would find every unique entry then output a results table of sorts, to display the total values for each matched 'person'

    Quote Originally Posted by marshy3300 View Post
    =sumproduct(--(A2:A900=Person1)*(C2:C900=Product1)*B2)

    Unverified this works, but sumproduct is what you want

  6. #6
    New Member
    Join Date
    Aug 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VLOOKUP help (not SUMIF)

    hmmm - this seems like it will work, but, again, as I have a pretty long list of "persons" in column A, many of which are the same, but are entered multiple times with different numbers and/or products in columns B and C respectively, this seems like a long workaround.

    Basically I have a table full of data and I want to sort it quickly so I can ascertain what Person 1 did, or, if looking at a product hierarchy, find out the sumtotal of the product in question.

    Quote Originally Posted by MARK858 View Post
    Why not SumIf and SumIfs if you want a combination of Person/Product?

    A B C D E F G H I J K
    1 Person 1 $200 Product 1 Person 1 1200 Product 1 700 Person 3 / Product 2 100
    2 Person 2 $300 Product 2 Person 2 300 Product 2 900 Person 1/ Product 1 700
    3 Person 3 $700 Product 3 Person 3 800 Product 3 700
    4 Person 1 $500 Product 2
    5 Person 3 $100 Product 2
    6 Person 1 $500 Product 1
    Sheet2

    Worksheet Formulas
    Cell Formula
    K1 =SUMIFS($B$1:$B$6,$A$1:$A$6,E3,$C1:$C6,G2)
    K2 =SUMIFS($B$1:$B$6,$A$1:$A$6,E1,$C$1:$C$6,G1)
    F1 =SUMIF($A$1:$A$6,E1,$B$1:$B$6)
    F2 =SUMIF($A$1:$A$6,E2,$B$1:$B$6)
    F3 =SUMIF($A$1:$A$6,E3,$B$1:$B$6)
    H1 =SUMIF($C$1:$C$6,G1,$B$1:$B$6)
    H2 =SUMIF($C$1:$C$6,G2,$B$1:$B$6)
    H3 =SUMIF($C$1:$C$6,G3,$B$1:$B$6)

  7. #7
    Board Regular MARK858's Avatar
    Join Date
    Nov 2010
    Location
    Southern England
    Posts
    11,101
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    2 Thread(s)

    Default Re: VLOOKUP help (not SUMIF)

    Why is it a long work around? you have a list of your unique names and just drag (or if the names are in the next column autofill) the formula down. There are only 3 formula there 1 for the people, 1 for the products and 1 if you wanted a combination of the 2 (they all work separately and are not reliant on each other).


    If you didn't have a list of the unique names or products you can even use a formula to get that.


    I am afraid that I don't see what your issue is.
    Test VBA on a copy of your data (remember you can't normally reverse the action)

    Please follow the forum Rules and Guidelines & please use Code tags around your code i.e. [CODE]your code[/CODE]

    To post a screenshot try one of these links
    MrExcel HTML Maker, RoryA addin (Win & Mac) or Borders-Copy-Paste

  8. #8
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,649
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    5 Thread(s)

    Default Re: VLOOKUP help (not SUMIF)

    This is actually a perfect situation to use a Pivot Table. Make sure your columns have headings, like this:

    ABC
    1PersonAmountProduct
    2Person 1$500 Product 1
    3Person 2$500 Product 2
    4Person 3$500 Product 3
    5Person 1$500 Product 2
    6Person 3$500 Product 2
    7Person 1$500 Product 1

    Sheet5





    Now select columns A:C. Go to the Insert tab, click the PivotTable button. Just click OK on the dialog box that opens. It now opens a new sheet. On the right is the PivotTable Fields box. Click on the Person heading and drag it to the Rows box below. Drag Product to the Columns box. Finally drag Amount to the Values box. Voila! You're done. It generates a sorted list of unique persons, and generates totals by product and person.
    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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