Help with formula

C

Cliff

A4=IF(COUNTIF(A1:AS1,1)=2,1,"")

The above formula searches a range of cells looking for the number 1
occurring 2 times and either returning 1 or leaving the cell blank.

How could the formula be changed to only do the search if cell A1 was equal
to the number 1?
 
C

Cliff

Thank you, it worked great.

One additional request, how could this one formula be changed to include the
additional range of cells at A6:E6 with the original range of A1:AS1?
 
S

SteveG

So you want to COUNTIF A1:AS1 and A6:E6 if they are equal to 1 and ad
them together to see if the sum = 2?

=IF(A1=1,IF(COUNTIF(A1:AS1,1)+COUNTIF(A6:E6,1)=2,1,""),"")

If you want it to look to see if either range's count of the number
returns a value of 2 then,

=IF(A1=1,IF(OR(COUNTIF(A1:AS1,1)=2,COUNTIF(A6:E6,1)=2),1,""),"")

HTH

Stev
 
Top