Using SUMPRODUCT with a VLOOKUP

Hi

I have a spreadsheet that goes as follows. Column A is Salesperson's name
and Column B is Achevied Sales Target (Yes or No). Column A gets it data
from anothr spreadsheet using a VLOOKUP table, hence each value is preceded
with a ' sign. C

I am using SUMPRODUCT to count the number of times a salesperson in A has
hit the target in B. The formula is as follows:

=SUMPRODUCT((I2:I22="YES")*(J2:J22="JOE"))

This only works if I retype "JOE" in the cell as text, deleting the v-lookup
formula. Obviously this is tedious and defeats the purpose of the vlookup.

Is there a way to correct my formula OR use a new formula?

Thanks!

Steph

Hi,

This is confusing.
Why does the data in Col A have a ' in front.
Yor narrative refers to columns A & B and your example formula refers to 2
different columns,

That aside try this
=SUMPRODUCT(--(I2:I22="YES")*--(J2:J22="JOE"))
Mike

"mae_bear22" wrote:

Hi

I have a spreadsheet that goes as follows. Column A is Salesperson's name
and Column B is Achevied Sales Target (Yes or No). Column A gets it data
from anothr spreadsheet using a VLOOKUP table, hence each value is preceded
with a ' sign. C

I am using SUMPRODUCT to count the number of times a salesperson in A has
hit the target in B. The formula is as follows:



This only works if I retype "JOE" in the cell as text, deleting the v-lookup
formula. Obviously this is tedious and defeats the purpose of the vlookup.

Is there a way to correct my formula OR use a new formula?

Thanks!

Steph

On Sep 15, 10:42*am, mae_bear22
wrote:
Hi

I have a spreadsheet that goes as follows. *Column A is Salesperson's name
and Column B is Achevied Sales Target (Yes or No). *Column A gets it data
from anothr spreadsheet using a VLOOKUP table, hence each value is preceded
with a ' sign. * C

I am using SUMPRODUCT to count the number of times a salesperson in A has
hit the target in B. *The formula is as follows:

=SUMPRODUCT((I2:I22="YES")*(J2:J22="JOE"))

This only works if I retype "JOE" in the cell as text, deleting the v-lookup
formula. *Obviously this is tedious and defeats the purpose of the vlookup. *

Is there a way to correct my formula OR use a new formula?

Thanks!

Steph

Steph-
Do you know why your Vlookup function is returning the salesperson's
name with the preceding tick (') mark in front of it? Does the source
data contain a tick?

IT WORKED!!! No one in my office could figure this one out!!!

Thank you!!!!

"mae_bear22" wrote:

Hi

I have a spreadsheet that goes as follows. Column A is Salesperson's name
and Column B is Achevied Sales Target (Yes or No). Column A gets it data
from anothr spreadsheet using a VLOOKUP table, hence each value is preceded
with a ' sign. C

I am using SUMPRODUCT to count the number of times a salesperson in A has
hit the target in B. The formula is as follows:

=SUMPRODUCT((I2:I22="YES")*(J2:J22="JOE"))

This only works if I retype "JOE" in the cell as text, deleting the v-lookup
formula. Obviously this is tedious and defeats the purpose of the vlookup.

Is there a way to correct my formula OR use a new formula?

Thanks!

Steph

Glad I could help

"mae_bear22" wrote:

IT WORKED!!! No one in my office could figure this one out!!!

Thank you!!!!

"mae_bear22" wrote:

Hi

I have a spreadsheet that goes as follows. Column A is Salesperson's name
and Column B is Achevied Sales Target (Yes or No). Column A gets it data
from anothr spreadsheet using a VLOOKUP table, hence each value is preceded
with a ' sign. C

I am using SUMPRODUCT to count the number of times a salesperson in A has
hit the target in B. The formula is as follows:

=SUMPRODUCT((I2:I22="YES")*(J2:J22="JOE"))

This only works if I retype "JOE" in the cell as text, deleting the v-lookup
formula. Obviously this is tedious and defeats the purpose of the vlookup.

Is there a way to correct my formula OR use a new formula?

Thanks!

Steph