Formula [Solved/Closed]

Jmccall2016 4 Posts Wednesday June 14, 2017Registration date June 15, 2017 Last seen - Jun 14, 2017 at 09:46 AM - Latest reply:  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
See more 

7 replies

Best answer
Mazzaropi 1834 Posts Monday August 16, 2010Registration dateContributorStatus May 30, 2018 Last seen - Jun 14, 2017 at 10:53 AM
1
Thank you
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

Thank you, Mazzaropi 1

Something to say? Add comment

CCM has helped 1706 users this month

Jmccall2016 4 Posts Wednesday June 14, 2017Registration date June 15, 2017 Last seen - Jun 14, 2017 at 12:47 PM
0
Thank you
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?
Change the cell references to suite your requirement.

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


If you can see the picture the formula is not adding up just the blanks. :(Thank you for your help with this.
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.
Jmccall2016 4 Posts Wednesday June 14, 2017Registration date June 15, 2017 Last seen - Jun 15, 2017 at 10:52 AM
There is nothing in the cells. They are infact blank.
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.