+ Reply to Thread
Results 1 to 4 of 4

COUNTIFS a range is not blank

  1. #1
    Registered User
    Join Date
    10-27-2012
    Location
    Uk
    MS-Off Ver
    Excel 2010
    Posts
    98

    Question COUNTIFS a range is not blank

    Hi All

    I have been struggling with this for a while, so it's time I ask the professionals

    The attached workbook has 2 sheets. Clients and Reports

    I am trying to add formulas to the Clients sheet that will show if a client has a certain report element across any of their reports

    I used a COUNTIFS formula for the SLA column which worked, but might not be the best solution.

    Essentially it's saying:
    IF you find the account number on the Reports sheet AND IF the [SLA] column on the reports sheet is not blank, then count it.
    Then replace anything great than 0 with an 'x' as the count value itself is not important


    Please Login or Register  to view this content.
    I was hoping to adapt this formula for the MI column on the Clients sheet, but this would require checking to see of a range of columns in the Reports sheet are not blank. The [Exec Summary]:[DNA & Canx] columns

    Please Login or Register  to view this content.
    But unfortunately this doesn't work

    It's likely I am approaching this the wrong way, so any/all advice welcome

    Many thanks in advance
    Vicky
    Attached Files Attached Files

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: COUNTIFS a range is not blank

    Try this:

    =IF(SUMPRODUCT((Reports_Table[GV Ref]=[@[GV Ref]])*(Reports_Table[[Exec Summary]:[DNA & Canx]]<>""))>0,"X","")
    Attached Files Attached Files
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Registered User
    Join Date
    10-27-2012
    Location
    Uk
    MS-Off Ver
    Excel 2010
    Posts
    98

    Re: COUNTIFS a range is not blank

    Glenn, thank you so much.
    That's not the first time you've saved my bacon either. Thank you truly!

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: COUNTIFS a range is not blank

    Hahaha. You're welcome.



    It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

    Finally, if that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

+ 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] COUNTIFS with blank cells
    By NiallC92 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 03-21-2021, 07:30 AM
  2. [SOLVED] COUNTIFS - Blank cells
    By blackburnsexcel in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-03-2021, 05:39 AM
  3. [SOLVED] Countifs Blank Workdays
    By Wrecking_Crew in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 12-08-2019, 11:02 AM
  4. [SOLVED] Need help making COUNTIFS formula display as blank if it's 'criteria cell' is blank
    By 27POP27 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-22-2018, 07:38 AM
  5. [SOLVED] COUNTIFS for non-blank cells
    By DSwartz in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-19-2013, 03:39 PM
  6. Countifs should return blank results for blank row of cells
    By Groovicles in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-29-2013, 06:20 PM
  7. [SOLVED] COUNTIFS Not Blank
    By whizbee in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-15-2013, 03:46 PM

Tags for this Thread

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