Help with formula

[Guest]

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?

 

[Guest]

=if(A1=1,true result, false result)

 

[SteveG]

Cliff,

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

HTH

Steve

 

[Guest]

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?

 

[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

 

[Guest]

Thanks, the first one is what I needed.

 

[SteveG]

Glad I could help.

Steve