SUMPRODUCT and vlookup

[bigd1320]

Hello,

How do I get SUMPRODUCT to read the vlookup cell to correlate the total for each industry type?

I apologize that my file is so large. I am not sure how to condense it anymore without confusing the problem. I removed a few month tabs to keep the size down.

If you look at the Tab NRCS SW Area you will notice SUMPRODUCT will find the UserName and add the time from each month. That is what I am looking for.

If you look at the Industry Usage tab B2 is supposed to find all of the "Agricultural' entries in column A in the Jan 2020 tab and add up the total time in column K. I assume this is because column A is a vlookup formula so SUMPRODUCT can't cross reference. Do I need to do something else for this formula to find the text in column A?

Thanks,
Dan

[ChemistB]

No file attached.

[bigd1320]

Sorry I thought I attached the file, but it was too large yet.

I am not sure why Feb 2020 works on the Industry Usage Tab. All I did was delete a number of entries to reduce the file size.

I would like to figure out Why January isn't working though.

Thanks,
Dan

[GeoffW283]

The jan problem (or at least one of them) is that you have a #N/A error on the "Jan 2020" worksheet at row 283. There's more errors on rows 392, 655, 762 etc.

Your Feb worksheet magically works Im guessing because there was a similar error that you got rid of when you deleted rows to reduce workbook size

[bigd1320]

So is there a limit to the amount of data I can run SUMPRODUCT on? Is there a better way to add up the duration of time?

I am not sure why the 'NRCS SW Area' tab worked correctly, even on the full spreadsheet.

Thanks,
Dan

[bigd1320]

I see. I needed to remove or remedy all of the NA# errors. The table now works.

Thanks for the help!

[Fluff13]

On the Jan tab change the formula in col A to
=IFERROR(VLOOKUP($D2,UserCode,3,0),"")
and the sumproduct should then work

[bigd1320]

That works great!!

Thanks a lot. I have been trying to figure this out for weeks!

Dan

[Fluff13]

You're welcome & thanks for the feedback.