I have the following formula that works just great, but I need to be able to
have "N/A" display if text is entered instead of a number value. Any
Suggestions?

=CHOOSE(SUMPRODUCT(--($A$1:$A$11=E27),--(G27=$B$1:$B$11),$D$1:$D$11)+SUMPRODUCT(--(E27=$A$13:$A$17),--(G27$B$13:$B$17),--(G27<=$C$13:$C$17),$D$13:$D$17),"Inconsistent","Do es Not Meet","Successful","Excellent","Exceptional")

Where exactly are you referring to when they enter text? Will this work(or
somethign similar)?

=IF(SUMPRODUCT(ISTEXT($A$1:$D$17))0,NA(),CHOOSE(S UMPRODUCT(--($A$1:$A$11=E27),--(G27=$B$1:$B$11),$D$1:$D$11)+SUMPRODUCT(--(E27=$A$13:$A$17),--(G27$B$13:$B$17),--(G27<=$C$13:$C$17),$D$13:$D$17),"Inconsistent","Do es
Not Meet","Successful","Excellent","Exceptional"))
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"jonssmaster" wrote:

I have the following formula that works just great, but I need to be able to
have "N/A" display if text is entered instead of a number value. Any
Suggestions?

=CHOOSE(SUMPRODUCT(--($A$1:$A$11=E27),--(G27=$B$1:$B$11),$D$1:$D$11)+SUMPRODUCT(--(E27=$A$13:$A$17),--(G27$B$13:$B$17),--(G27<=$C$13:$C$17),$D$13:$D$17),"Inconsistent","Do es Not Meet","Successful","Excellent","Exceptional")

I am entering the value in cell G27, G28, etc. The formula is in cell H27,
H28, etc. and usually the entry will be a percentage, but if there is
vacation or some other reason that a percentage is not recorded for the week,
I need to have them enter a text like "Vac" or "N/A". Does that answer the
question?

"Luke M" wrote:

Where exactly are you referring to when they enter text? Will this work(or
somethign similar)?

=IF(SUMPRODUCT(ISTEXT($A$1:$D$17))0,NA(),CHOOSE(S UMPRODUCT(--($A$1:$A$11=E27),--(G27=$B$1:$B$11),$D$1:$D$11)+SUMPRODUCT(--(E27=$A$13:$A$17),--(G27$B$13:$B$17),--(G27<=$C$13:$C$17),$D$13:$D$17),"Inconsistent","Do es
Not Meet","Successful","Excellent","Exceptional"))
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"jonssmaster" wrote:

I have the following formula that works just great, but I need to be able to
have "N/A" display if text is entered instead of a number value. Any
Suggestions?

=CHOOSE(SUMPRODUCT(--($A$1:$A$11=E27),--(G27=$B$1:$B$11),$D$1:$D$11)+SUMPRODUCT(--(E27=$A$13:$A$17),--(G27$B$13:$B$17),--(G27<=$C$13:$C$17),$D$13:$D$17),"Inconsistent","Do es Not Meet","Successful","Excellent","Exceptional")