+ Reply to Thread
Results 1 to 3 of 3

Need help making COUNTIFS formula display as blank if it's 'criteria cell' is blank

  1. #1
    Registered User
    Join Date
    11-12-2018
    Location
    Norway
    MS-Off Ver
    Excel 2016
    Posts
    36

    Need help making COUNTIFS formula display as blank if it's 'criteria cell' is blank

    As the title says, I need help with for instance making this COUNTIFS formula:

    =COUNTIFS($B$2:B2843;B2843; $C$2:C2843;C2843) to display as blank if the red criteria cells are blank. In the sheet, columns MNO are variations of this formula, and column P uses a COUNTIFS to check if the Part number (in column C) exists also in another sheet (to check if an inspected part is on the actual inspection list).

    Sorry in advance, as this is quite an advanced excel sheet from the start, but I've tried to shorten down and remove any irrelevant information. You would only need to solve this issue for the O column for instance, and the idea would most likely apply to all other columns. Feel free to hide columns J,M,N and P also, just to solve the O column. Here's a picture of the filtered sheet:
    2.png

    In the picture below you can see how it looks at the moment with all relevant columns visible when I copy these formulas downwards. They function just fine, but I'd like them to be blank if nothing else is written on the line in the C column.
    1.png

    I've tried making another =IF(formula;formula;"") but that doesn't work with an already existing COUNTIFS. And I've tried conditional formatting to where I can make the row display transparent text if the row's C cell is blank, but haven't managed that to work.

    The excel sheet is attached.

    Any ideas?

    (Just an explanation of the columns:
    - M counts the number of consequtive 'Positive' inspections.
    - N counts the total amount of 'Negative' inspections.
    - O counts the total amount of inspections.
    - P checks to see if the inspected part number on the LOG is on our Inspection List sheet.)
    Attached Files Attached Files
    Last edited by 27POP27; 11-22-2018 at 07:30 AM.

  2. #2
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Need help making COUNTIFS formula display as blank if it's 'criteria cell' is blank

    there's no reason a pre-emptive test should not work, i.e.

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    the above will also improve performance, aside formatting, as the COUNTIFS will only calculate on rows where it is required.

  3. #3
    Registered User
    Join Date
    11-12-2018
    Location
    Norway
    MS-Off Ver
    Excel 2016
    Posts
    36

    Re: Need help making COUNTIFS formula display as blank if it's 'criteria cell' is blank

    Quote Originally Posted by XLent View Post
    there's no reason a pre-emptive test should not work, i.e.

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    the above will also improve performance, aside formatting, as the COUNTIFS will only calculate on rows where it is required.
    Aaah yes of course. For some reason I didn't think of a simple solution like that. I was trying to add more lines into the COUNTIFS formula or using IFS with conditional formatting. Thank you!

+ 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 formula keeps counting blank cell with formulas.
    By Big.Moe in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-03-2017, 08:22 PM
  2. Display blank cell if previous cell is blank in formula
    By Brent951 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-29-2016, 03:34 PM
  3. Countifs (How to give criteria for blank cell).
    By Shradharani in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 01-18-2013, 07:44 AM
  4. VLOOKUP to display a blank cell for certain criteria
    By avidcat in forum Excel General
    Replies: 1
    Last Post: 12-04-2011, 12:16 PM
  5. Excel 2007 : Formula making a cell blank
    By Harlort in forum Excel General
    Replies: 3
    Last Post: 10-27-2011, 01:21 PM
  6. Replies: 1
    Last Post: 02-06-2008, 08:23 AM
  7. Replies: 3
    Last Post: 04-21-2006, 12:55 PM

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