Home |
Search |
Today's Posts |
#2
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Here's one way. It assumes the option codes are entered in the range
B1:B9, which you'll need to change to match your spreadsheet. This formula assumes that numbers such as 319 and 403 are entered as numbers, not text. If they are entered as text, use the second version of the formula. The formula results in TRUE or FALSE: if all 5 constituent codes for ZPP are present, the formula results in TRUE. You can use that in an IF statement to handle the ZPP situation as appropriate. =AND(SUMPRODUCT(--(B1:B9=319))=1,SUMPRODUCT(--(B1:B9="4NA"))=1,SUMPRODUCT(--(B1:B9=430))=1,SUMPRODUCT(--(B1:B9=431))=1,SUMPRODUCT(--(B1:B9=403))=1) If option code numbers are entered as text: =AND(SUMPRODUCT(--(B1:B9="319"))=1,SUMPRODUCT(--(B1:B9="4NA"))=1,SUMPRODUCT(--(B1:B9="430"))=1,SUMPRODUCT(--(B1:B9="431"))=1,SUMPRODUCT(--(B1:B9="403"))=1) |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Sorting | New Users to Excel | |||
Sorting Data in ascending order | Excel Discussion (Misc queries) | |||
Advice sought: Multiple column sorting | Excel Worksheet Functions | |||
SORTING question | New Users to Excel | |||
Adding a KeyID column for sorting | New Users to Excel |