Formula [Solved/Closed]

Posts
4
Registration date
Wednesday June 14, 2017
Status
Member
Last seen
June 15, 2017
- - 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 

3 replies

Best answer
Posts
1833
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
May 30, 2018
131
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

Say "Thank you" 1

A few words of thanks would be greatly appreciated. Add comment

CCM 5777 users have said thank you to us this month

Posts
4
Registration date
Wednesday June 14, 2017
Status
Member
Last seen
June 15, 2017
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"})))
Posts
4
Registration date
Wednesday June 14, 2017
Status
Member
Last seen
June 15, 2017
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
Posts
4
Registration date
Wednesday June 14, 2017
Status
Member
Last seen
June 15, 2017
-
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.