# Thread: VLOOKUP help (not SUMIF) Thanks: 0 Likes:  2 Post #5329688 (1)Post #5330288 (1)

1. ## 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)

Thanks.

2. ## 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

3. ## Re: VLOOKUP help (not SUMIF)

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

Unverified this works, but sumproduct is what you want

4. ## 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)

5. ## 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'

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

Unverified this works, but sumproduct is what you want

6. ## 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.

Originally Posted by MARK858
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. ## 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.

8. ## 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.