# IF Formula to look at a range of cells?

1. ## IF Formula to look at a range of cells?

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

in C3

try

4. ## Re: IF Formula to look at a range of cells?

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")

5. ## Re: IF Formula to look at a range of cells?

One way...

This array formula** entered in C3:

** 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.

6. ## Re: IF Formula to look at a range of cells?

I like Jonmo's solution. Clever.

7. ## Re: IF Formula to look at a range of cells?

Originally Posted by Hawkeye16
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..

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1