VLOOKUP help (not SUMIF)

cakiwi

New Member
Joined
Aug 21, 2019
Messages
3
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:
ABC
Person 1$500Product 1
Person 2$500Product 2
Person 3$500Product 3
Person 1$500Product 2
Person 3$500Product 2
Person 1$500Product 1

<tbody>
</tbody>


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)

AB
Person 1$1500
Person 2$500
Person 3$1000

<tbody>
</tbody>

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.
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
maybe use PivotTable

PersonValueProductPersonSum of ValueProductSum of Value
Person 1
500​
Product 1Person 1
1500​
Product 1
1000​
Person 2
500​
Product 2Person 2
500​
Product 2
1500​
Person 3
500​
Product 3Person 3
1000​
Product 3
500​
Person 1
500​
Product 2
Person 3
500​
Product 2
Person 1
500​
Product 1
 
Upvote 0
=sumproduct(--(A2:A900=Person1)*(C2:C900=Product1)*B2)

Unverified this works, but sumproduct is what you want
 
Upvote 0
Why not SumIf :confused: and SumIfs if you want a combination of Person/Product?


Book1
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
Cell Formulas
RangeFormula
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:
Upvote 0
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'

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

Unverified this works, but sumproduct is what you want
 
Upvote 0
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.

Why not SumIf :confused: 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

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
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)

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
 
Upvote 0
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.
 
Upvote 0
This is actually a perfect situation to use a Pivot Table. Make sure your columns have headings, like this:


Book1
ABC
1PersonAmountProduct
2Person 1$500Product 1
3Person 2$500Product 2
4Person 3$500Product 3
5Person 1$500Product 2
6Person 3$500Product 2
7Person 1$500Product 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.
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top