[Solved] COUNTIF for non blank and blank cells

Discuss the spreadsheet application
Post Reply
luofeiyu
Posts: 21
Joined: Thu Sep 14, 2017 2:11 am

[Solved] COUNTIF for non blank and blank cells

Post by luofeiyu »

To count non blank with =countif(a2:a12;"<>""")
non-blank.png
Why can't count blank with =countif(a2:a12;"=""")
blank.png
Last edited by MrProgrammer on Mon Oct 31, 2022 9:11 pm, edited 2 times in total.
Reason: Tagged ✓ [Solved] -- MrProgrammer, forum moderator
LibreOffice 4.3.3.2 on Debian 8
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: countif for non blank and blank cells

Post by FJCC »

I showed how to count blank cells here using the SUMPRODUCT() function. OpenOffice distinguishes between a cell that contains nothing and a cell that contains a formula that returns an empty string. In your example, put this formula in A5

Code: Select all

=IF(A1 = 8; 1; "")
The formula

Code: Select all

=COUNTIF(A2:A12;"")
will count that cell. It will not count the blank cells.
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: countif for non blank and blank cells

Post by Villeroy »

There is a function to count blanks:
=COUNTBLANK(A1:F99)

All cells minus non-blanks:
=ROWS(A1:F99)*COLUMNS(A1:F99)-COUNTA(A1:F99)
COUNTA counts all non-empty cells, COUNT counts numbers only.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Post Reply