[Solved] How to determine # of cells not blank in selection?

Discuss the spreadsheet application
Post Reply
swampwiz
Posts: 32
Joined: Wed Mar 17, 2010 11:30 am

[Solved] How to determine # of cells not blank in selection?

Post by swampwiz »

I have a spreadsheet that has a number of columns in which for every row there could be some columns that have text. I would like to get the number of such column whose row in question has some text.

What I could do is simply have another set of columns, each of which would map to the original columns and have a simple formula

=IF(A1="";0;1)

and then simply sum up the cells for a particular row. However, this would add a lot more stuff to the spreadsheet that would not have to be done were there a nice function that could it for me, such as

=NUMBEROFNONEMPTYCELLS(A1:Z1)

which would simply give this number. So, e.g., if everything between A1 & Z1 were blank except for C1, Q1 & X1, then this function would return 3.
Last edited by robleyd on Mon Jul 19, 2021 4:20 am, edited 1 time in total.
Reason: Tag [Solved]
Windows XP Professional, Version 5.1, Service Pack 3
OpenOffice 4.1.2 (perhaps later since swampwiz doesn't provide accurate information here -- MrProgrammer, forum moderator)
Mountaineer
Posts: 310
Joined: Sun Sep 06, 2020 8:27 am

Re: How to determine # of cells that are not blank in select

Post by Mountaineer »

Check for SUMPRODUCT - there are some creative possibilities.

https://www.libreofficehelp.com/sumprod ... fice-calc/

J
OpenOffice 3.1 on Windows Vista
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: How to determine # of cells that are not blank in select

Post by Villeroy »

=NUMBEROFNONEMPTYCELLS(A1:Z1)

Code: Select all

=COUNTA(A1:Z1)
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
swampwiz
Posts: 32
Joined: Wed Mar 17, 2010 11:30 am

Re: How to determine # of cells that are not blank in select

Post by swampwiz »

Villeroy wrote:
=NUMBEROFNONEMPTYCELLS(A1:Z1)

Code: Select all

=COUNTA(A1:Z1)
This worked great!
Windows XP Professional, Version 5.1, Service Pack 3
OpenOffice 4.1.2 (perhaps later since swampwiz doesn't provide accurate information here -- MrProgrammer, forum moderator)
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] How to determine # of cells not blank in select

Post by Villeroy »

COUNTBLANK(A1:Z99) counts blanks
COUNT(A1:Z99) counts numbers
COUNTA(A1:Z99) counts numbers and text (all data are either text or number).
COUNTA(A1:Z99)-COUNT(A1:Z99) counts text.
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
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: [Solved] How to determine # of cells not blank in select

Post by Zizi64 »

_______________________________________________
Windows XP Professional, Version 5.1, Service Pack 3
OpenOffice 3.1.1, Build 9420
Please update your signature in this forum.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
Post Reply