Formula

Solved/Closed
Jmccall2016 Posts 4 Registration date Wednesday June 14, 2017 Status Member Last seen June 15, 2017 - Jun 14, 2017 at 09:46 AM
 x - Jun 15, 2017 at 01:13 PM
I am trying to find a formula that will add up all blank fields in a range of cells. For example, if cell 1,2,3 have data and 4,5 are blank I want the answer to show 2.

Can someone help me come up with a formula for this?

Jennifer

3 replies

Mazzaropi Posts 1985 Registration date Monday August 16, 2010 Status Contributor Last seen May 24, 2023 147
Jun 14, 2017 at 10:53 AM
Jennifer, Good morning.

Suppose your range of data:
A1:A100


Try to use:
=COUNTBLANK(A1:A100)

Is that what you want?
I hope it helps.
--
Belo Horizonte, Brasil.
Marcílio Lobão
1
Jmccall2016 Posts 4 Registration date Wednesday June 14, 2017 Status Member Last seen June 15, 2017
Jun 14, 2017 at 12:47 PM
How can I add all the blanks if they are not in order? The data I am requesting is say A1, B1, D1, F1.. etc?
0
Change the cell references to suite your requirement.

=SUMPRODUCT(COUNTBLANK(INDIRECT({"A1","B1","D1","F1"})))
0
Jmccall2016 Posts 4 Registration date Wednesday June 14, 2017 Status Member Last seen June 15, 2017
Jun 14, 2017 at 01:13 PM


If you can see the picture the formula is not adding up just the blanks. :(Thank you for your help with this.
0
What is actually in those 'blank' cells?
A 'space' is not blank for example.
A result of a formula formatted as a blank is not blank.
0
Jmccall2016 Posts 4 Registration date Wednesday June 14, 2017 Status Member Last seen June 15, 2017
Jun 15, 2017 at 10:52 AM
There is nothing in the cells. They are infact blank.
0
just noticed that the cells that are colored are in fact:
AR4 AP4 AN4 and AL4

unless you have hidden rows.
or that you really are intending to count row 1 in which case I have no idea.
0