VLOOKUP Multiple re-occurring codes in the database

Hello

I am attempting to create a cost analysis for a pro-forma for my work using VLOOKUP function. I use VLOOKUP to bring up the number of units assigned to a certain code, however my tables often have the same code multiple times and I need to add the units assigned to these codes without changing the table. VLOOKUP only brings up the first unit assigned to the lookup code. Is there any way to use a combination of SUM and VLOOKUP in order to add all the units together. Please see the attachment.

In the attachmentm the Units column I have entered some units that I looked up myself in order to test my VLOOKUP function. The codes I am referencing are under the J column under Care HCPC. If you scroll down to row 47, code 38525 you will see that there should be 8 units showing up under column S under All Other for surgeries but only one shows up. There are eight instances of the code showing up in the table in the "Surgery All Other" tab. Is there any way to add these up without changing the table?

Hi ehernandez,

Martin is correct, you should use SUMIFS or just SUMIF functions.

This is what I would use in Q3 = SUMIF('Surgery MCare'!$A$2:$A$41,D3,'Surgery MCare'!$G$2:$G$41), then use the same logic to the rest of the columns.

You have to make sure to use the dollar signs ($) to fix your range. Without the $ signs, the range was changing too, which I don't think you want.

Look up what the SUMIF formula and what it needs... type the formula and a helper should pop-up for more info.

Good luck,
Dennis

Thank you for the quick response, the SUMIF function worked wonderfully .