+ Reply to Thread
Results 1 to 6 of 6

Stop Countif from counting blank cells

  1. #1
    Registered User
    Join Date
    11-25-2019
    Location
    Adelaide, South Australia
    MS-Off Ver
    Office 365
    Posts
    13

    Stop Countif from counting blank cells

    Question in 2 parts (refer attached spreadsheet)

    Using Office 365

    1. Using formula =IFERROR(INDEX($O$3:$O$29, MATCH(0,INDEX(COUNTIF($B$36:B36,$O$3:$O$29),0,0),0)),"") is there a way to stop column B in the bottom table form inserting 0 when it encounters a mismatch (i.e can 0 be changed to a blank cell)? I want Cell B39 to be blank.

    2. Using formula =COUNTIF($O$3:$O$29,"="&$B37) is there a way to stop column C in the bottom table from counting blanks? I want Cells C40:C54 to be blanks (like Cell C39).

    New User so apologies if this is not in the correct thread.

    Thanks Andrew
    Attached Files Attached Files

  2. #2
    Forum Expert azumi's Avatar
    Join Date
    12-10-2012
    Location
    YK, Indonesia
    MS-Off Ver
    Excel 2019
    Posts
    2,360

    Re: Stop Countif from counting blank cells

    Perhaps, put this on C37 and dragged down:

    =IF($B37="","",COUNTIF($O$3:$O$29,$B37))

  3. #3
    Registered User
    Join Date
    11-25-2019
    Location
    Adelaide, South Australia
    MS-Off Ver
    Office 365
    Posts
    13

    Re: Stop Countif from counting blank cells

    Thanks Azumi - that worked perfectly for point 2.

    Any ideas on point 1 and how to remove the 0 from cell B39?

  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,899

    Re: Stop Countif from counting blank cells

    =IFERROR(INDEX($O$3:$O$29, MATCH(0,INDEX(COUNTIF($B$36:B37,$O$3:$O$29),0,0),0))&"","")

    will fix that for you.
    Attached Files Attached Files
    Last edited by Glenn Kennedy; 11-26-2019 at 06:10 AM.
    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

  5. #5
    Registered User
    Join Date
    11-25-2019
    Location
    Adelaide, South Australia
    MS-Off Ver
    Office 365
    Posts
    13

    Re: Stop Countif from counting blank cells

    Thanks Glenn - that did the trick. Cheers Andrew

  6. #6
    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,899

    Re: Stop Countif from counting blank cells

    You're welcome.



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

    It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

+ 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. Replies: 9
    Last Post: 01-19-2018, 03:57 AM
  2. [SOLVED] COUNTA, SUMPRODUCT, COUNTIF, Counting blank cells.
    By BlindAlley in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-09-2017, 11:47 AM
  3. Replies: 9
    Last Post: 08-17-2015, 04:05 PM
  4. [SOLVED] Countif if counting 65536 cells (excel 2003) with count if blank
    By Sc0tt1e in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 12-06-2013, 11:48 AM
  5. COUNTIF without counting blank cells
    By Melgaard in forum Excel General
    Replies: 5
    Last Post: 03-03-2010, 11:48 PM
  6. Counting should stop at blank cell
    By roberto1111 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-09-2009, 01:22 PM
  7. My Countif formula is not accurately counting- Need to cound blank cells
    By mrgillus in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-10-2009, 02:59 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