# Excel - COUNT Function Problem - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

i need to adjust a formula to not count blank cells
Solved by Z. J. in 60 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 18/03/2018 - 03:10
Hi Welcom to got it1
User 18/03/2018 - 03:10
G'Day mate I'm steve
Excelchat Expert 18/03/2018 - 03:10
Good day steve!
User 18/03/2018 - 03:10
ok so ill paste what i can just a second
Excelchat Expert 18/03/2018 - 03:10
So what can I do for you Steve?
Excelchat Expert 18/03/2018 - 03:10
Sure mate
User 18/03/2018 - 03:12
ok so as you may know this formula counts how many times a name in F2 appears in A:A
User 18/03/2018 - 03:12
but it also counts the blank cells and i don't want that
Excelchat Expert 18/03/2018 - 03:12
Yes, Steve
User 18/03/2018 - 03:13
so how do i need to rewrite the formula to say count only the names and not blank spaces?
Excelchat Expert 18/03/2018 - 03:13
Ahh, so can we input values on A column and F column for more concrete scenario?
Excelchat Expert 18/03/2018 - 03:14
is that okay, Can i Input values mate?
User 18/03/2018 - 03:15
i have the formula that lists the unique names in column A:A
User 18/03/2018 - 03:16
you see this is an array so it needs to be fixed to work in sheets
Excelchat Expert 18/03/2018 - 03:16
Ahh I see, steve
Excelchat Expert 18/03/2018 - 03:18
Hmmm
Excelchat Expert 18/03/2018 - 03:18
May I ask if you're familiar with the Counta function?
User 18/03/2018 - 03:18
i think the formula needs array at the start somehow
User 18/03/2018 - 03:19
you see everything works in my real file but it also counts the blank cells and i don't want that because i total the counts
Excelchat Expert 18/03/2018 - 03:20
Ahhh
Excelchat Expert 18/03/2018 - 03:21
Steve, is it all right for you to upload your file here?
Excelchat Expert 18/03/2018 - 03:22
So that we can actually see the scenario? thanks mate
User 18/03/2018 - 03:23
sorry i can not it is on another work computer that I'm accessing through a laptop
Excelchat Expert 18/03/2018 - 03:24
Ahhh, its okay, but in here i think we have an error in F Column?
Excelchat Expert 18/03/2018 - 03:25
btw, based on your samples on column a, what would be the output in column g?
Excelchat Expert 18/03/2018 - 03:26
You still there mate?
User 18/03/2018 - 03:27
ok so the formula in column F lists the unique names in A:A and the formula in G counts the number of times that unique name appears in A:A
Excelchat Expert 18/03/2018 - 03:27
Owwww, okay
User 18/03/2018 - 03:28
but the problem is that it also counts the number of times a blank cell appears in A:A and i don't want that figure as i sum F:F later on
Excelchat Expert 18/03/2018 - 03:28
Okay wait mate
Excelchat Expert 18/03/2018 - 03:30
Wait ill generate my formula mate
User 18/03/2018 - 03:30
ok
User 18/03/2018 - 03:30
is that one which will list all the unique names?
User 18/03/2018 - 03:30
or one that does the count?
Excelchat Expert 18/03/2018 - 03:30
one that does the count
User 18/03/2018 - 03:30
ok
User 18/03/2018 - 03:31
so it will count the number of times each name in F appears ?
Excelchat Expert 18/03/2018 - 03:31
yes
Excelchat Expert 18/03/2018 - 03:32
Is this right mate?
User 18/03/2018 - 03:33
ok 1 second
Excelchat Expert 18/03/2018 - 03:33
yes sure
User 18/03/2018 - 03:34
joe only appears once
User 18/03/2018 - 03:34
not twice
Excelchat Expert 18/03/2018 - 03:34
wait mate
User 18/03/2018 - 03:34
ok
Excelchat Expert 18/03/2018 - 03:34
as we can see in the formula bar of G1
Excelchat Expert 18/03/2018 - 03:35
our data should start at row 2
User 18/03/2018 - 03:35
ok?
Excelchat Expert 18/03/2018 - 03:36
is this right?
User 18/03/2018 - 03:36
it won't start at row 2 anyway it will be around row 4 or something in my real sheet
User 18/03/2018 - 03:36
Excelchat Expert 18/03/2018 - 03:36
ok ok
Excelchat Expert 18/03/2018 - 03:36
so is this correct now?
User 18/03/2018 - 03:37
i sec
Excelchat Expert 18/03/2018 - 03:37
yes sure
User 18/03/2018 - 03:37
thats the formula I had originally in that cell
Excelchat Expert 18/03/2018 - 03:38
Yes, and i think only the references were a bit off earlier
Excelchat Expert 18/03/2018 - 03:38
countif function should be enough here
User 18/03/2018 - 03:38
no
User 18/03/2018 - 03:39
the references were a bit off in the formula i had in column F
User 18/03/2018 - 03:39
hat was my exact formula in column G
Excelchat Expert 18/03/2018 - 03:39
Ahhh
Excelchat Expert 18/03/2018 - 03:39
Ok
User 18/03/2018 - 03:39
and as that formula is it will also count the blank cells in column A:A
Excelchat Expert 18/03/2018 - 03:39
Ahhh
User 18/03/2018 - 03:40
i don't want it to count the blank cells
Excelchat Expert 18/03/2018 - 03:40
So the F column is the one we should be working on?
User 18/03/2018 - 03:40
it has to look across the reference A:A2000 but not count blank cells
User 18/03/2018 - 03:40
dont worry about the F column!
Excelchat Expert 18/03/2018 - 03:40
Ok,
User 18/03/2018 - 03:41
i have a formula that will list the unique names from A:A in the F column already
Excelchat Expert 18/03/2018 - 03:41
I will focus on G column, sorry steve
User 18/03/2018 - 03:41
in my real sheet
User 18/03/2018 - 03:41
the ONLY problem i have is that the formula in column G:G counts the blank cells from A:A as well as the names
Excelchat Expert 18/03/2018 - 03:41
ahh
Excelchat Expert 18/03/2018 - 03:42
you mean it displays "0" on blank cells?
User 18/03/2018 - 03:42
no
Excelchat Expert 18/03/2018 - 03:43
In this case, we did not count the blank cells right?
Excelchat Expert 18/03/2018 - 03:43
Joe have 1
Excelchat Expert 18/03/2018 - 03:43
Dave has 2
User 18/03/2018 - 03:43
in F:F my formula lists the unique names in column \$A4:\$A2000
User 18/03/2018 - 03:43
wait
Excelchat Expert 18/03/2018 - 03:43
Ok
Excelchat Expert 18/03/2018 - 03:44
Ok I'll wait
User 18/03/2018 - 03:44
in G:G my formula counts the number of times a name in A:A exists using the names list in F:F but it also counts the blank cells
Excelchat Expert 18/03/2018 - 03:44
AHhhh
Excelchat Expert 18/03/2018 - 03:45
You mean there is additional value in column g which corresponds to the count of blank cells?
User 18/03/2018 - 03:46
yes
Excelchat Expert 18/03/2018 - 03:46
Oww okay
Excelchat Expert 18/03/2018 - 03:46
If that's the case
User 18/03/2018 - 03:46
each cell in G:G that counts from the reference F:F which is a blank cell counts the blank cells which gives a number like for example 1938
Excelchat Expert 18/03/2018 - 03:47
we should use if function in that case, Steve
User 18/03/2018 - 03:47
ok so can you make one?
Excelchat Expert 18/03/2018 - 03:47
Yes mate
Excelchat Expert 18/03/2018 - 03:47
wait
User 18/03/2018 - 03:47
you mean if function for G:G?
Excelchat Expert 18/03/2018 - 03:47
yes mate,
User 18/03/2018 - 03:47
ok try then show and ill look
Excelchat Expert 18/03/2018 - 03:47
Sure
Excelchat Expert 18/03/2018 - 03:49
I have it now Steve,
Excelchat Expert 18/03/2018 - 03:49
the formula i generated looks like this
User 18/03/2018 - 03:49
ok ill look
Excelchat Expert 18/03/2018 - 03:49
=if(F2 <> "", COUNTIF(\$A\$2:\$A\$200,F2),"")
User 18/03/2018 - 03:50
ok it looks great ill try it in my real sheet one minute please
Excelchat Expert 18/03/2018 - 03:50
it means that if the F value is blank, it would display no value
Excelchat Expert 18/03/2018 - 03:50
sure mate
User 18/03/2018 - 03:50
yes i get it great
User 18/03/2018 - 03:50
one sec
User 18/03/2018 - 03:55
this didn't work in my real file hang on
User 18/03/2018 - 03:56
it did something else when i dragged the formula down the column
User 18/03/2018 - 03:56
wait a sec
Excelchat Expert 18/03/2018 - 03:56
sure mate
User 18/03/2018 - 03:57
it doesn't work it gives me like 3,2,2,1,0, numbers that do not represent the count at all
Excelchat Expert 18/03/2018 - 03:57
yes, i think because of the referencing
User 18/03/2018 - 03:57
hang on i found it
User 18/03/2018 - 03:57
wait
Excelchat Expert 18/03/2018 - 03:57
noticed earlier that G2 is referring to the F3 value
Excelchat Expert 18/03/2018 - 03:57
User 18/03/2018 - 03:58
ok got it
Excelchat Expert 18/03/2018 - 03:58
NIce to hear it mate
User 18/03/2018 - 03:59
ill try it one more time in the other laptop in the file that is on the remote pc
User 18/03/2018 - 03:59
just a few minutes
Excelchat Expert 18/03/2018 - 03:59
Ok sure Steve
User 18/03/2018 - 04:05
nearly there
Excelchat Expert 18/03/2018 - 04:07
Ok Steve, :)
User 18/03/2018 - 04:08
not yet
Excelchat Expert 18/03/2018 - 04:09
if you have further clarification, we can extend our session
User 18/03/2018 - 04:09
yes
Excelchat Expert 18/03/2018 - 04:09
how was it?
User 18/03/2018 - 04:09
i tried it in the cell on my other sheet but it counted the blanks

This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user information.

Get instant expert help with Excel and Google Sheets

#### Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.