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