CODEWITHSUNDEEP
excelrangevlookupsumifs
Maria C.
Maria C.

Reputation: 11

How to sumif the vlookup value of a range?

I'm not sure how to explain what I am trying to achieve so I will start with the data I am working with:

1) A table that lists IDs corresponding to Games. Each game has several IDs.

2) A table that lists IDs and $ Earned on those IDs.

In another table, I have the list of games and want to return the sum of $ Earned on those games:

Tables 1,2,3

I was trying to do this with a combination of a SUMIF and VLOOKUP formula but I can't seem to find a way to do that because the VLOOKUP returns a value rather than a range. What I want to do is sum a sum_range ($ earned) if the lookup value of the range (game corresponding to the code) is a match.

I could simply add a column to the second table that returns the game of that code with a VLOOKUP. But the problem is that this would need to be done in a very large number of sheets, and with each time the new data is received.

Hopefully that made sense and thank you in advance for any help I can get!

Cheers, Maria

Upvotes: 1

Views: 852

Answers (2)

Forward Ed
Forward Ed

Reputation: 9894

Well this may be a bit of a cheat. if you look at your game code, you are either looking for basketball or baseball. So we could do a sum if the code contains one of those words.

If case sensitivity is important
=SUMPRODUCT(--(ISNUMBER(FIND("Basketball",B2:B16)))*C2:C16)

or if case sensitivity is not important 
=SUMPRODUCT(--(ISNUMBER(SEARCH("Basketball",B2:B16)))*C2:C16)

B2:B16 would be your code in table 2
C2:C16 would be your $ earned column in C2
The formula would be placed where the ? cell is beside big win Basketball.

updated option for keyword

So if you are not looking for just baseball or basketball, but the entire string of the key words that you are looking for the total for, you could use the following provided the words in table three form part of the code when the spaces are removed.

=SUMPRODUCT(--(ISNUMBER(FIND(SUBSTITUTE(B19," ",""),B2:B16)))*C2:C16)
or
=SUMPRODUCT(--(ISNUMBER(SEARCH(SUBSTITUTE(B19," ",""),B2:B16)))*C2:C16)

This assumes the keyword your are looking for in the code is in B19. The substitute function removes the spaces to match your code.

Upvotes: 1

trincot
trincot

Reputation: 351369

In the second table add a (hidden) column, where you perform a VLOOKUP in the first table of the Code, retrieving the Game name.

Now you have something to base your SUMIF on: the value in the additional, hidden column should match the Game you have in your summary.

Upvotes: 0

Related Questions