SUMPRODUCT Issue

[oddworld]

hi all I would really appreciate some assistance with a sumproduct issue.
my current formula is looking up an employee number in sheet summary from a2 and a proficiency number in g1, then check to see if that employee has that proficiency recorded within table Accomp and return a yes or no.

what I need help with is I need to add an and or statement. I need to add another proficiency number listed in sheet summary cell and n1 and still return a yes or no. So look up employee and look for the two proficiencies listed I g1,n1 and return yes/no

This what I am using atm

=IF(SUMPRODUCT(--(Accomp!$A$2:$A$40000=Summary!A3),--(Accomp!$K$2:$K$40000=Summary!$G$1))=1,"Yes","No")

Regards,
Odd

 

[Aladin Akyurek]

Which range must satisfy this n1?

 

[oddworld]

hi, the summary sheet contains a unique list of pers id numbers in column A, and proficiency numbers are in g1 & n1. the source data is in sheet Accomp - pers id in column A and proficiency numbers in column K. I need the formula to answer if an employee has either a proficiency listed in G1 or N1. An employee wont have both it will be one or the other.

 

[Aladin Akyurek]

hi, the summary sheet contains a unique list of pers id numbers in column A, and proficiency numbers are in g1 & n1. the source data is in sheet Accomp - pers id in column A and proficiency numbers in column K. I need the formula to answer if an employee has either a proficiency listed in G1 or N1. An employee wont have both it will be one or the other.

=IF(SUMPRODUCT(--(Accomp!$A$2:$A$40000=Summary!A3),--(Accomp!$K$2:$K$40000=Summary!$G$1))=1,"Yes","No")

In B3 of Summary enter and copy down:

=IF(SUMPRODUCT(COUNTIFS(Accomp!$A$2:$A$40000,$A3,Accomp!$K$2:$K$40000,CHOOSE({1,2},$G$1,$N$1))),"yes","no")​

 

[oddworld]

Wow, a gold star to you Sir, thank you very much.

 

[Aladin Akyurek]

Wow, a gold start to you Sir, thank you very much.

You are welcome.