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 |
Using SUMPRODUCT with a VLOOKUP
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 |
Using SUMPRODUCT with a VLOOKUP
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? |
Using SUMPRODUCT with a VLOOKUP
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 |
Using SUMPRODUCT with a VLOOKUP
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 |
All times are GMT +1. The time now is 01:07 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com