VLOOKUP help (not SUMIF)

C

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:
[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]Person 1[/TD]
[TD]$500[/TD]
[TD]Product 1[/TD]
[/TR]
[TR]
[TD]Person 2[/TD]
[TD]$500[/TD]
[TD]Product 2[/TD]
[/TR]
[TR]
[TD]Person 3[/TD]
[TD]$500[/TD]
[TD]Product 3[/TD]
[/TR]
[TR]
[TD]Person 1[/TD]
[TD]$500[/TD]
[TD]Product 2[/TD]
[/TR]
[TR]
[TD]Person 3[/TD]
[TD]$500[/TD]
[TD]Product 2[/TD]
[/TR]
[TR]
[TD]Person 1[/TD]
[TD]$500[/TD]
[TD]Product 1[/TD]
[/TR]
</tbody>[/TABLE]


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)

[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]Person 1[/TD]
[TD]$1500[/TD]
[/TR]
[TR]
[TD]Person 2[/TD]
[TD]$500[/TD]
[/TR]
[TR]
[TD]Person 3[/TD]
[TD]$1000[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Sort by date Sort by votes
maybe use PivotTable

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td]Person[/td][td]Value[/td][td]Product[/td][td][/td][td=bgcolor:#DDEBF7]Person[/td][td=bgcolor:#DDEBF7]Sum of Value[/td][td][/td][td=bgcolor:#DDEBF7]Product[/td][td=bgcolor:#DDEBF7]Sum of Value[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Person 1[/td][td]
500​
[/td][td]Product 1[/td][td][/td][td]Person 1[/td][td]
1500​
[/td][td][/td][td]Product 1[/td][td]
1000​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Person 2[/td][td]
500​
[/td][td]Product 2[/td][td][/td][td]Person 2[/td][td]
500​
[/td][td][/td][td]Product 2[/td][td]
1500​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Person 3[/td][td]
500​
[/td][td]Product 3[/td][td][/td][td]Person 3[/td][td]
1000​
[/td][td][/td][td]Product 3[/td][td]
500​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Person 1[/td][td]
500​
[/td][td]Product 2[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Person 3[/td][td]
500​
[/td][td]Product 2[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Person 1[/td][td]
500​
[/td][td]Product 1[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]
 
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'

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

Unverified this works, but sumproduct is what you want
Click to expand...
 
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.

MARK858 said:
Why not SumIf :confused: and SumIfs if you want a combination of Person/Product?

ABCDEFGHIJK
Person 1Product 1Person 1Product 1Person 3 / Product 2
Person 2Product 2Person 2Product 2Person 1/ Product 1
Person 3Product 3Person 3Product 3
Person 1Product 2
Person 3Product 2
Person 1Product 1

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]$200[/TD]

[TD="align: right"][/TD]

[TD="align: right"]1200[/TD]

[TD="align: right"]700[/TD]
[TD="align: right"][/TD]

[TD="align: right"]100[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]$300[/TD]

[TD="align: right"][/TD]

[TD="align: right"]300[/TD]

[TD="align: right"]900[/TD]
[TD="align: right"][/TD]

[TD="align: right"]700[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]$700[/TD]

[TD="align: right"][/TD]

[TD="align: right"]800[/TD]

[TD="align: right"]700[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]$500[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]$100[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]$500[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]K1[/TH]
[TD="align: left"]=SUMIFS($B$1:$B$6,$A$1:$A$6,E3,$C1:$C6,G2)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]K2[/TH]
[TD="align: left"]=SUMIFS($B$1:$B$6,$A$1:$A$6,E1,$C$1:$C$6,G1)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F1[/TH]
[TD="align: left"]=SUMIF($A$1:$A$6,E1,$B$1:$B$6)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F2[/TH]
[TD="align: left"]=SUMIF($A$1:$A$6,E2,$B$1:$B$6)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F3[/TH]
[TD="align: left"]=SUMIF($A$1:$A$6,E3,$B$1:$B$6)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H1[/TH]
[TD="align: left"]=SUMIF($C$1:$C$6,G1,$B$1:$B$6)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H2[/TH]
[TD="align: left"]=SUMIF($C$1:$C$6,G2,$B$1:$B$6)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H3[/TH]
[TD="align: left"]=SUMIF($C$1:$C$6,G3,$B$1:$B$6)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...
 
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
You must log in or register to reply here.

Similar threads

P
  • Solved
create a report summary page by group
Replies
4
Views
224
PippaThePointer
P
S
  • Question Question
Excel VBA - Sort & Get Totals - Logic
Replies
4
Views
426
STARL0RD
S
M
  • Question Question
Sum together the larger amount of two ranges by row
Replies
3
Views
70
Cubist
Cubist
C
  • Question Question
How to SUMIF based on criteria from another sheet?
Replies
6
Views
393
Cubist
Cubist
H
  • Solved
Reduce size of (or speed up) excel spreadsheet
Replies
5
Views
170
helpcharity
H

Forum statistics

Threads
1,224,743
Messages
6,180,686
Members
452,994
Latest member
Janick

Share this page

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