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.
[Solved] How to determine # of cells not blank in selection?
[Solved] How to determine # of cells not blank in selection?
Last edited by robleyd on Mon Jul 19, 2021 4:20 am, edited 1 time in total.
Reason: Tag [Solved]
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)
OpenOffice 4.1.2 (perhaps later since swampwiz doesn't provide accurate information here -- MrProgrammer, forum moderator)
-
- Posts: 318
- Joined: Sun Sep 06, 2020 8:27 am
Re: How to determine # of cells that are not blank in select
Check for SUMPRODUCT - there are some creative possibilities.
https://www.libreofficehelp.com/sumprod ... fice-calc/
J
https://www.libreofficehelp.com/sumprod ... fice-calc/
J
OpenOffice 3.1 on Windows Vista
Re: How to determine # of cells that are not blank in select
=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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: How to determine # of cells that are not blank in select
This worked great!Villeroy wrote:=NUMBEROFNONEMPTYCELLS(A1:Z1)Code: Select all
=COUNTA(A1:Z1)
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)
OpenOffice 4.1.2 (perhaps later since swampwiz doesn't provide accurate information here -- MrProgrammer, forum moderator)
Re: [Solved] How to determine # of cells not blank in select
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.
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: [Solved] How to determine # of cells not blank in select
Please update your signature in this forum._______________________________________________
Windows XP Professional, Version 5.1, Service Pack 3
OpenOffice 3.1.1, Build 9420
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.
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.