Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help needed for Sumproduct or Other Conditional testing
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help needed for Sumproduct or Other Conditional testing
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 On Oct 23, 3:24*pm, claude jerry wrote: * * *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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help needed for Sumproduct or Other Conditional testing
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 "claude jerry" wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help needed for Sumproduct or Other Conditional testing
Thanks
Both Work Fine "Mike H" wrote: 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 "claude jerry" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct help needed. | Excel Discussion (Misc queries) | |||
IF and SUMPRODUCT help needed | Excel Discussion (Misc queries) | |||
conditional formatting -- testing for multiple words | Excel Discussion (Misc queries) | |||
Min/Max help needed with a SumProduct | Excel Worksheet Functions | |||
SUMPRODUCT help needed | Excel Worksheet Functions |