Results 1 to 4 of 4

Thread: Formula Help - Multiple COUNTIF's to validate if value is in range - Excel 2016

  1. #1
    Board Regular Johnny Thunder's Avatar
    Join Date
    Apr 2010
    Location
    California
    Posts
    556
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Formula Help - Multiple COUNTIF's to validate if value is in range - Excel 2016

    Hello All,

    I have a formula that looks at 3 different sheets to validate if the value is in a range but I need help updating or using another method to enter the forumla.

    Here is what I got so far but its not all the way there.

    Formula: =IF(COUNTIF('Drama & Comedy'!$J$7:$L$33,"*"&E2&"*")>0=TRUE,"Backlog",IF(COUNTIF('Unscripted, Film, Kids'!$J$7:$L$33,"*"&E2&"*")>0=TRUE,"Backlog",IF(COUNTIF(Oprah!$J$7:$L$33,"*"&E2&"*")>0=TRUE,"Backlog","Grid")))

    The jist of what is needed that is missing from the formula, is for each Countif that looks at different sheets, if the value is completely missing, mark it with "Not Available", If it is in range J7:L33 then "Backlog", if it is in range N7:T33 then "Grid".

    I don't know how to enter the piece about "Not available" or how to modify what I currently have to suit.

    Any help is appreciated. Also, the formula can't be an array formula since users won't be interacting with that sheet. Its something that I am flagging on the back end of the spreadsheet.
    Regards,
    JT

    Please follow the forum Rules and Guidelines

    To post a screenshot try one of these links

    MrExcel HTML Maker, RoryA addin (Win & Mac) or Borders-Copy-Paste

  2. #2
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,659
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Formula Help - Multiple COUNTIF's to validate if value is in range - Excel 2016

    Maybe:

    =IF(SUMPRODUCT(COUNTIF(INDIRECT({"'Drama & Comedy'","'Unscripted, Film, Kids'","Oprah"}&"!J7:L33"),"*"&E2&"*")),"Backlog",IF(SUMPRODUCT(COUNTIF(INDIRECT({"'Drama & Comedy'","'Unscripted, Film, Kids'","Oprah"}&"!N7:T33"),"*"&E2&"*")),"Grid","Not Available"))
    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

  3. #3
    Board Regular Johnny Thunder's Avatar
    Join Date
    Apr 2010
    Location
    California
    Posts
    556
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula Help - Multiple COUNTIF's to validate if value is in range - Excel 2016

    Formula worked like a charm! Thanks for all the help on this. Got me past my roadblock.
    Regards,
    JT

    Please follow the forum Rules and Guidelines

    To post a screenshot try one of these links

    MrExcel HTML Maker, RoryA addin (Win & Mac) or Borders-Copy-Paste

  4. #4
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,659
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Formula Help - Multiple COUNTIF's to validate if value is in range - Excel 2016

    You're welcome!

    Glad it worked for you.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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