sun results of a vlookup across a range

[Lord Kelvan]

Hi

I am trying to sum the results of a vlookup in one cell

I have a series of cells

B3:b37

Which have a series of text values

These match up to a lookup table on another sheet

Othersheet!a3:b8

I want to lookup each of the values in b3:b37 and match them to the
lookup table and return the sum of these values

I have looked around the net and found some things but none seem to
work this one worked but the value it returns is incorrect

=SUMPRODUCT(--(ISNUMBER(MATCH(INDEX(othersheet!
A3:b8,,1),B3:B37,0))),INDEX(othersheet!A3:b8,,2))

Any assistance would be helpful

Regards
Kelvan

 

[Don Guillett Excel MVP]

Hi

I am trying to sum the results of a vlookup in one cell

I have a series of cells

B3:b37

Which have a series of text values

These match up to a lookup table on another sheet

Othersheet!a3:b8

I want to lookup each of the values in b3:b37 and match them to the
lookup table and return the sum of these values

I have looked around the net and found some things but none seem to
work this one worked but the value it returns is incorrect

=SUMPRODUCT(--(ISNUMBER(MATCH(INDEX(othersheet!
A3:b8,,1),B3:B37,0))),INDEX(othersheet!A3:b8,,2))

Any assistance would be helpful

Regards
Kelvan

"If desired, send your file to dguillett @gmail.com I will only look
if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results."

 

[Don Guillett Excel MVP]

Hi

I am trying to sum the results of a vlookup in one cell

I have a series of cells

B3:b37

Which have a series of text values

These match up to a lookup table on another sheet

Othersheet!a3:b8

I want to lookup each of the values in b3:b37 and match them to the
lookup table and return the sum of these values

I have looked around the net and found some things but none seem to
work this one worked but the value it returns is incorrect

=SUMPRODUCT(--(ISNUMBER(MATCH(INDEX(othersheet!
A3:b8,,1),B3:B37,0))),INDEX(othersheet!A3:b8,,2))

Any assistance would be helpful

Regards
Kelvan

"If desired, send your file to dguillett @gmail.com I will only look
if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results."

 

[zvkmpw]

I have a series of cells

B3:b37

Which have a series of text values

These match up to a lookup table on another sheet

Othersheet!a3:b8

I want to lookup each of the values in b3:b37 and match them to the
lookup table and return the sum of these values

One way is to start by putting this in C3
=SUMIF(othersheet!$A$3:$A$7, B3, othersheet!$B$3:$B$7)
and copying down to C37.

This gives the sum for each of the text values individually.

If you need the grand total, sum column C.

If necessary, column B can be hidden.

Be aware that SUMIF() is case-insensitive; that is, it considers "A"
to be the same as "a".