Sum values duplicate category but different values vertically with VLOOKUP

RLarson17

New Member
Joined
Feb 2, 2020
Messages
5
Office Version
  1. 2019
Platform
  1. Windows
I saw a thread Using VLOOKUP to sum values cells vertically that was having the same problem, but didn't really get an answer.

I am using VLOOKUP to pull categories from reports I'm getting from a management software =VLOOKUP(A3,$B$3:$C$846,1,0). This will give a category such we can call NG. In another column I've tried using =SUMIF(C:C,VLOOKUP(D3,$B$3:$C$846,2,0)) to sum all values with duplicate categories. This will work if the value of a category is the same, but if the corresponding value is different it will only pull the first value of the VLOOKUP.

What I'd like to do is sort the categories using the VLOOKUP (it gives me the correct spacing to transplant those values into other excel calculations) and sum the corresonding values associated with the duplicate categories. Is this possible or another way to go about it?
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
As you can see from the example the Vlook sum will workfor NP and NR, but not for NB, RV3 and Icon
 

Attachments

  • Excel VLookup Sum Problem Example.gif
    Excel VLookup Sum Problem Example.gif
    117.3 KB · Views: 37
Upvote 0
Hi and welcome to MrExcel!
I didn't quite understand how your data is and what results you need.
You could put 2 images, 1 with the original information and another image with the result that you need.
If possible try using the XL2BB tool.
 
Upvote 0
I'm not familiar with XL2bb tool, but will look in to it. I've attached images below. The Example Data is with the pasted data separated to the right. I would paste the categories under "Category Paste" into Column B and the Values from "Value Paste" in to Column C.

The second image Color Code is the desired result but I used a simple sum function to show what I wanted. The first image was what I was getting with the sumif and VLOOKup formulas. The VLOOKUP sumif will work for NP and NR, but not for NB, RV3 and Icon
 

Attachments

  • Excel VLookup Sum Problem Example Data.gif
    Excel VLookup Sum Problem Example Data.gif
    142.5 KB · Views: 16
  • Excel VLookup Sum Desired Result Color Code.gif
    Excel VLookup Sum Desired Result Color Code.gif
    83.9 KB · Views: 18
Upvote 0
Book1
ABCDEF
2Category StandardCategory PasteValue PasteVlook CatVlook Sum Value
3Nex4-10Total Value for DW:14.54#N/A$0.00
4Nex10-24F#N/A$0.00
5Nex24-60NB770#N/A$0.00
6Nex60-120NB789.1#N/A$0.00
7NBRV335.4NB$770.00
8NBFRV3177#N/A$0.00
9NOFTotal Value for FLEA:1771.5#N/A$0.00
10NOHW#N/A$0.00
11NPFNP160.96#N/A$0.00
12NPNR193.46NP$482.88
13NRNP160.96NR$386.92
14NRFNP160.96#N/A$0.00
15PS23NR193.46#N/A$0.00
16PS4Total Value for HW:869.8#N/A$0.00
17PS45INJ#N/A$0.00
18IconIcon308.9Icon$308.90
19qqAAiCADI40181.75#N/A$0.00
20RV3Icon324.35RV3$35.40
Sheet2
Cell Formulas
RangeFormula
D3:D20D3=VLOOKUP(A3,$B$5:$C$848,1,0)
E3:E20E3=SUMIF(C:C,VLOOKUP(D3,$B$5:$C$848,2,0))
 
Upvote 0
Why not just use =SUMIF(B:B,A3,C:C)

ZFluff.xlsm
ABCDEF
1
2Category StandardCategory PasteValue PasteVlook CatVlook Sum ValueSumIf
3Nex4-10Total Value for DW:14.54#N/A00
4Nex10-24F#N/A00
5Nex24-60NB770#N/A00
6Nex60-120NB789.1#N/A00
7NBRV335.4NB7701559.1
8NBFRV3177#N/A00
9NOFTotal Value for FLEA:1771.5#N/A00
10NOHW#N/A00
11NPFNP160.96#N/A00
12NPNR193.46NP482.88482.88
13NRNP160.96NR386.92386.92
14NRFNP160.96#N/A00
15PS23NR193.46#N/A00
16PS4Total Value for HW:869.8#N/A00
17PS45INJ#N/A00
18IconIcon308.9Icon308.9633.25
19qqAAiCADI40181.75#N/A00
20RV3Icon324.35RV335.4212.4
Team 1
Cell Formulas
RangeFormula
D3:D20D3=VLOOKUP(A3,$B$5:$C$848,1,0)
E3:E20E3=SUMIF(C:C,VLOOKUP(D3,$B$5:$C$848,2,0))
F3:F20F3=SUMIF(B:B,A3,C:C)
 
Upvote 0
The second image Color Code is the desired result

Thanks for using XL2BB.

Try this

Book1
ABCD
1
2Category StandardCategory PasteValue PasteSUM
3Nex4-10Total Value for DW:14.54$0.00
4Nex10-24F$0.00
5Nex24-60NB770$0.00
6Nex60-120NB789.1$0.00
7NBRV335.4$1,559.10
8NBFRV3177$0.00
9NOFTotal Value for FLEA:1771.5$0.00
10NOHW$0.00
11NPFNP160.96$0.00
12NPNR193.46$482.88
13NRNP160.96$386.92
14NRFNP160.96$0.00
15PS23NR193.46$0.00
16PS4Total Value for HW:869.8$0.00
17PS45INJ$0.00
18IconIcon308.9$633.25
19qqAAiCADI40181.75$0.00
20RV3Icon324.35$212.40
Sheet2
Cell Formulas
RangeFormula
D3:D20D3=SUMIF($B$3:$B$50,A3,$C$3:$C$50)
 
Upvote 0
Thank you! I was definitely way over complicating it. Still learning all the helpful things excel can do. Thanks again!
 
Upvote 0
Glad we could help & thanks for the feedback
 
Upvote 0
I'm glad to help you. Thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,521
Messages
6,114,104
Members
448,548
Latest member
harryls

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