Formula [Solved/Closed]

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

Posts
1834
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
September 24, 2020
135
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
Thank you

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

CCM 2942 users have said thank you to us this month

Posts
4
Registration date
Wednesday June 14, 2017
Status
Member
Last seen
June 15, 2017

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



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.
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.