+ Reply to Thread
Results 1 to 17 of 17

Countif blank or not.

  1. #1
    Forum Contributor
    Join Date
    05-06-2017
    Location
    London, United Kingdom
    MS-Off Ver
    2016
    Posts
    279

    Countif blank or not.

    Hi guys, so i am trying to think of how i can do this.. and if i can!

    So for example, i want to count the blank cells in column H, but only if column B has text in it.

    how would i do so?

  2. #2
    Forum Contributor
    Join Date
    03-30-2016
    Location
    Gillette, WY
    MS-Off Ver
    2016
    Posts
    220

    Re: Countif blank or not.

    Try:

    =COUNTBLANK(H:H)-COUNTA(B:B)

    That would give you the number of cells that are blank in both columns H and B. Is that what you're looking for? Your post is a little unclear.

  3. #3
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    6,773

    Re: Countif blank or not.

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


    BSB

  4. #4
    Forum Contributor
    Join Date
    05-06-2017
    Location
    London, United Kingdom
    MS-Off Ver
    2016
    Posts
    279
    Quote Originally Posted by TFiske View Post
    Try:

    =COUNTBLANK(H:H)-COUNTA(B:B)

    That would give you the number of cells that are blank in both columns H and B. Is that what you're looking for? Your post is a little unclear.
    Sorry, if column B1 has text in it then count H1 if H1 is blank

    But if B2 is empty donít count H2, if that makes sense!

  5. #5
    Forum Contributor
    Join Date
    05-06-2017
    Location
    London, United Kingdom
    MS-Off Ver
    2016
    Posts
    279

    Re: Countif blank or not.

    But it has to count The whole H1:H50 for example doing as above!

  6. #6
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    6,773

    Re: Countif blank or not.

    Attach a sample workbook and show your desired outcome.

    Make sure the data is representative of your real workbook but desensitized if appropriate.

    BSB

  7. #7
    Forum Contributor
    Join Date
    05-06-2017
    Location
    London, United Kingdom
    MS-Off Ver
    2016
    Posts
    279

    Re: Countif blank or not.

    Attached the workbook.
    Attached Files Attached Files

  8. #8
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    6,773

    Re: Countif blank or not.

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


    BSB

  9. #9
    Forum Contributor
    Join Date
    05-06-2017
    Location
    London, United Kingdom
    MS-Off Ver
    2016
    Posts
    279
    Quote Originally Posted by BadlySpelledBuoy View Post
    Try:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    BSB
    Worked, thank you ☺️

  10. #10
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    6,773

    Re: Countif blank or not.

    Happy to help

    BSB

  11. #11
    Forum Contributor
    Join Date
    05-06-2017
    Location
    London, United Kingdom
    MS-Off Ver
    2016
    Posts
    279

    Re: Countif blank or not.

    New problem with it! its counting the empty cells in B3:B25 if they have a formula in it! ahh which i don't need.. only if the formula result is there.

  12. #12
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    6,773

    Re: Countif blank or not.

    That is why I mentioned your sample workbook should be representative of the real workbook!

    Try this:
    =COUNTIFS(B3:B25,"?*",H3:H25,"")

    BSB

  13. #13
    Forum Contributor
    Join Date
    05-06-2017
    Location
    London, United Kingdom
    MS-Off Ver
    2016
    Posts
    279

    Re: Countif blank or not.

    Didn't realize i was going to use formula's till i decided to add them, but that worked.. thanks

  14. #14
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    6,773

    Re: Countif blank or not.

    No probs at all mate.

    BSB

  15. #15
    Registered User
    Join Date
    05-26-2013
    Location
    New Jersey, US
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Countif blank or not.

    I need to do the same thing only I need to count the blanks in column J where columns H and I have text. Can you add an AND?

  16. #16
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,475

    Re: Countif blank or not.

    Quote Originally Posted by kbalfour View Post
    . . . I need to count the blanks in column J where columns H and I have text. . . .
    COUNTIFS works on an AND basis. Conditions in each range-condition pair must be satisfied to be included in the count.

    =COUNTIFS(J3:J25,"",H3:H25,"?*",I3:I25,"?*")

    Note: the 1st condition, "", includes both truly blank cells as well as cells evaluating to "". If that's what you want, fine. If you want only truly blank cells, you need to use

    =COUNTIFS(J3:J25,"=",H3:H25,"?*",I3:I25,"?*")

    OTOH, if you only want cells evaluating to "" but not truly blank cells, that requires the obscure

    =COUNTIFS(J3:J25,"*",J3:J25,"",H3:H25,"?*",I3:I25,"?*")

    where the first pair checks that cells are text, and the second that they're blank or evaluate to "", combined they mean only text = "".

  17. #17
    Registered User
    Join Date
    05-26-2013
    Location
    New Jersey, US
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Countif blank or not.

    Thank you...I'm going to try that and see what happens.

+ 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] COUNTIF needs to count blank
    By rz6657 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-17-2016, 01:34 PM
  2. [SOLVED] Countif until blank cell (mutiple blank cells)
    By plasma33 in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 05-07-2015, 01:14 PM
  3. CountIf blank with two columns
    By gibsongk55 in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 02-26-2010, 04:52 AM
  4. Countif only when a cell is blank.
    By Stevie-B in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-03-2008, 10:58 AM
  5. [SOLVED] How to COUNTIF blank cells?
    By Soapman in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-31-2006, 05:30 PM
  6. [SOLVED] Countif not blank
    By Darby in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-07-2005, 04:05 PM
  7. COUNTIF:counting blank ones.
    By luvthavodka in forum Excel General
    Replies: 3
    Last Post: 07-06-2005, 12:05 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