IF Formula to look at a range of cells?

[jono7gold]

I have a range of cells B2 to B30 with a choice of 2 different text values - 'Rads' or 'UFH'. In cell C3 I need a formula to look at the cell range B2 to B30 - if all the cells say Rads then C3 will say Rads, if they say UFH then C3 will say UFH but if B2 to B30 say both Rads and UFH then C3 say both.

Im sure it is a very simple formula but help will be appriciated.

Thanks

Jon

[Special-K]

in C3
=IF(COUNTIF(B2:B30,"Rads")=29,"Rads",IF(COUNTIF(B2:B30,"UFH")=29,"UFH","Both"))

[Ace_XL]

try

=IF(COUNTIF(B2:B30,"Rads")=COUNTA(B2:B30),"Rads",IF(COUNTIF(B2:B30,"UFH")=COUNTA(B2:B30),"UFH","Both"))

[Jonmo1]

Try

=IF(COUNTIF(B2:B30,B2)=29,B2,"Both")

To make it more robust, so you don't have to manually come up with the 29

=IF(COUNTIF(B2:B30,B2)=ROWS(B2:B30),B2,"Both")

[Tony Valko]

One way...

This array formula** entered in C3:

=IF(COUNTA(B2:B30)>1,IF(AND(B2:B30="Rads"),"Rads",IF(AND(B2:B30="UFH"),"UFH","Both")),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

[Hawkeye16]

I like Jonmo's solution. Clever.

[Jonmo1]

I like Jonmo's solution. Clever.

Thanks.
It is assuming that the range literally is ALL either 1 or the other value.
And there are no blanks.

If there are blanks, and we wanted to check all NON blanks for the criteria, it could be
=IF(COUNTIF(B2:B30,B2)=COUNTA(B2:B30),B2,"Both")
That's probably better than using rows anyway..