How to LOOKUP and ADD values

[movetastic]

Hi folks,

I have a worksheet that tracks materials losses and who lost the materials. I would like to be able to search this worksheet for every instance that an employee was associated with a material loss and then output the total dollar amount of losses associated with that employee.

I have tried using VLOOKUP like so: =VLOOKUP(A3,'Material Losses'!$J$2:$V$5000,11,FALSE)

• A3 is the name of the employee.
• The range of J2 - V5000 is a range of where the employees name could be found. But, because multiple employees can be associated with the same loss, it could be in multiple different columns.
• Column 11 is where I store the total dollar amount.

The difficulty here is that the employees name can be in several different columns and that even when I can make the formula return a value it only returns the first value, not a SUM total of all dollar amounts found in column 11. Can anyone point me in the right direction for solving this?

Thanks!

[FDibbins]

Hi and welcome to the forum

to begin with, vlookup will only return the very 1st instance of a "find" and then stop looking. You probably need to look at creating a table of names and then using sumif() or sumproduct()

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[movetastic]

Thanks for the response FDibbins. I think SumIf is a better option. I attached a sample workbook with my feeble attempt at the SumIf formula. It is getting closer but still need some work - maybe you can help?

[FDibbins]

Thanks for the file.

Based on your sample, try this in C2, copied down...
=SUMPRODUCT((Losses!$C$2:$C$4)*(Losses!$D$2:$G$4=Roster!A2))

(I used C so that you can see your original answers)

[movetastic]

That worked... but I am not exactly sure how. Why do you multiply? More importantly, THANKS!

[movetastic]

Hey all, I am continuing work on this spreadsheet but have come across another problem. When including a blank row while using the SUMPRODUCT function, I receive a #VALUE! error. I need to include thousands of blank rows in the product because the spreadsheet functions as a constantly updated running tally. The rows are updated as we identify losses throughout the year. I have a attached a simplified example.

Thanks again, this forum has already proven to be a massive help!

[Jonmo1]

It's not a problem with blanks...

Both ranges need to use the same number of rows...

So
=SUMPRODUCT((Losses!$C$2:$C$4)*(Losses!$D$2:$G$5=Roster!A2))
should be
=SUMPRODUCT((Losses!$C$2:$C$5)*(Losses!$D$2:$G$5=Roster!A2))

[movetastic]

Thanks for the help Jonmo, that did fix things in my attached example, but what about this line:

=SUMPRODUCT('Material Losses'!$G$2:$G$5000)*('Material Losses'!$J$2:$M$5000=Roster!A3)

Sorry to be so clueless.

[Jonmo1]

You're missing a set of ()

=SUMPRODUCT(('Material Losses'!$G$2:$G$5000)*('Material Losses'!$J$2:$M$5000=Roster!A3))

[movetastic]

You're right! But, alas, the problem persists. I think the issue is that below ROW 3 there are errors, but I have it set so the error don't display, like so: =IFERROR(VLOOKUP(B4,'Materials Pricing'!A$2:B$40,2,FALSE),"")

If the IFERROR function were not there, I would have an #N/A - is this the source of the issue? I could PM you the file if you'd like.

Thanks!

[movetastic]

You're right! But, alas, the problem persists. I think the issue is that below ROW 3 there are errors, but I have it set so the error don't display, like so: =IFERROR(VLOOKUP(B4,'Materials Pricing'!A$2:B$40,2,FALSE),"")

If the IFERROR function were not there, I would have an #N/A - is this the source of the issue? I could PM you the file if you'd like.

Thanks!

[Jonmo1]

It's the "" result of those formulas causing the issue with the SUMPRODUCT

Try making those return 0 instead of ""

=IFERROR(VLOOKUP(B4,'Materials Pricing'!A$2:B$40,2,FALSE),0)

[movetastic]

That did the trick - thanks so much!

[Jonmo1]

You're welcome.