+ Reply to Thread
Results 1 to 7 of 7

IF Formula to look at a range of cells?

  1. #1
    Registered User
    Join Date
    05-08-2013
    Location
    Norwich
    MS-Off Ver
    Excel 2010
    Posts
    51

    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

  2. #2
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    HOME: Excel 2007 - WORK: Excel 2013
    Posts
    7,007

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

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

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  3. #3
    Forum Guru Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

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

    try

    =IF(COUNTIF(B2:B30,"Rads")=COUNTA(B2:B30),"Rads",IF(COUNTIF(B2:B30,"UFH")=COUNTA(B2:B30),"UFH","Both"))
    Life's a spreadsheet, Excel!
    Say thanks, Click *

  4. #4
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    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")
    Last edited by Jonmo1; 07-18-2014 at 08:49 AM.

  5. #5
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

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

    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.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  6. #6
    Valued Forum Contributor Hawkeye16's Avatar
    Join Date
    02-27-2013
    Location
    Holland
    MS-Off Ver
    ├┤ Pew Pew
    Posts
    441

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

    I like Jonmo's solution. Clever.
    Despite the high cost of living, it remains very popular.

    Don't forget to mark threads SOLVED when you get an answer and rep all the geniouses that helped you today!

  7. #7
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

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

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Return non-blank cells from range AND ignore cells with only formula w/o results
    By grafitti in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-06-2014, 08:15 AM
  2. Replies: 1
    Last Post: 09-04-2013, 07:23 AM
  3. Replies: 1
    Last Post: 01-23-2013, 03:00 PM
  4. [SOLVED] VBA Copy a range of cells values using the range of cells in a formula (which will change)
    By murtaep in forum Excel Programming / VBA / Macros
    Replies: 32
    Last Post: 06-05-2012, 07:59 AM
  5. Replies: 2
    Last Post: 05-31-2012, 05:37 AM

Bookmarks

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