Originally Posted by

**Bob Phillips**
Actually, you have an error in that first formula, it should be

=IF(F$1="","",IF(OR(COUNTIF(F$1:F$27,$A30)>0,COUNTIF(F$1:F$27,$B30)>0,COUNTIF(F$1:F$27,$C30)>0,COUNTIF(F$1:F$27,$D30)>0),

SUM(COUNTIF(F$1:F$27,$A30),COUNTIF(F$1:F$27,$B30),COUNTIF(F$1:F$27,$C30),COUNTIF(F$1:F$27,$D30)),0))

but it can be simplified to

=IF(F$1="","",SUMPRODUCT(COUNTIF(F$1:F$27,$A30:$D30)))

and the second can be reduced to

=IF(S$1="","",--(SUMPRODUCT(--(COUNTIF(S$1:S$27,$N30:$Q30)>0))=4))

## Bookmarks