VLOOKUP and SUM together

[MaeglinBC3]

Hi all,

This is probably a really simple fix but would be great if someone could explain where I've gone wrong.

I'm having problems with VLOOKUP - I think it's doing the right thing here (i.e looking at the right data) but it isn't counting all the data with 'Bill & Ben' as a reference it just gives me either 1 or 3 as an answer instead of 9 that the answer should be. See manually put in data for the exact.

Book2.xlsx

Please can someone fix this minor problem for me?

Ben

[BadlySpelledBuoy]

Hi Ben,

VLOOKUP will find the first instance of Bill & Ben and return the corresponding value.

If you want the result to be 9 (i.e. the sum of column AF for all Bill & Ben rows) then try the below formula:

Please Login or Register  to view this content.

BSB

[Kaper]

How about SUMIF? (Vlookup is not a good function here - it returns just one value):

Please Login or Register  to view this content.

[MaeglinBC3]

Thanks BSB,

That works exactly how I needed it to, there was me thinking I needed VLOOKUP.

Solved some major problems there.

Ben

[BadlySpelledBuoy]

Happy to help mate.

Don't forget to mark the thread as SOLVED if you're happy you have your solution.

BSB

[ben_hensel]

There are a couple fundamental errors going on in this spreadsheet.

1) The data on the "Score Sheet" is set up like an old-timey scorecard from the pre-computer days; as a rule, never use merged cells with any sort of math or lookup operation, and avoid "spacer" rows if at all possible.

So my first response is, rebuild your data table layout so that it's more cooperative.

2) VLOOKUP finds the first match, not all the matches. That's how it's designed, it can't really do what you're asking for, without some kind of ugly array-function hacks. You fundamentally want to run a function that adds up all the values based on a criteria.

Fortunately those exist directly, to wit:

Please Login or Register  to view this content.

You can copy/paste that in all the cells in the C7:D13 area and it will deliver the same result as the Manual Analysis tab.

But after that, I think you're going to run into some pretty troublesome hassles for getting data for each Player, for example.