Help needed for Sumproduct or Other Conditional testing

C

claude jerry

A B C D E F
1 Name Colour Red Blue Pink Check Point
2 Bob Red 1 ok
3 Rob Blue 1 ok
4 Tom Pink 1 error
5 Sam Red 1 Error


User Enters the Following
Name in Col A
Colour in Col B and depending on Which Colour he has entered he will type a
digit in the Colour Col (Col C, D or E)

I want a formula to be entered in Col F, Which tells me if the users have
done it right, "Ok" If its Correct and "Error" if its not correct

E.g in above Sam Selected Colour Red but has entered the Digit in Col D
"Blue" this is wrong. he should enter the digit in col C Red
 
M

muddan madhu

try this

put this formula in F2 =IF(HLOOKUP(B2,$C$1:$E$5,ROW(2:2),
0)=1,"ok","error") and drag it down
 
M

Mike H

Hi,

Put this in F2 and drag down

=IF(OR(SUMPRODUCT((B2:$B$5=B2)*($C$1:$E$1=B2)*(C2:$E$5))=0,COUNT(C2:E2)>1),"Error","OK")

Mike
 
C

claude jerry

Thanks

Both Work Fine

Mike H said:
Hi,

Put this in F2 and drag down

=IF(OR(SUMPRODUCT((B2:$B$5=B2)*($C$1:$E$1=B2)*(C2:$E$5))=0,COUNT(C2:E2)>1),"Error","OK")

Mike
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top