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
ll adjust the references
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
ok take your time
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.
