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

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
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
199
PippaThePointer
P
S
  • Question Question
Excel VBA - Sort & Get Totals - Logic
Replies
4
Views
352
STARL0RD
S
C
  • Question Question
How to SUMIF based on criteria from another sheet?
Replies
6
Views
382
Cubist
Cubist
R
  • Solved
Scoring table
Replies
9
Views
610
Rock5150
R
S
  • Question Question
Please help Excel gives 00-Jan-00
Replies
6
Views
238
etaf
etaf

Forum statistics

Threads
1,223,704
Messages
6,173,984
Members
452,540
Latest member
haasro02

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