**Question description:**

*This user has given permission to use the problem statement for this blog.*

i need to adjust a formula that sums a column but only sums the values and not blanks or zeros

Solved by V. L. in 40 mins

This is the chat thread from the real Excelchat help session. It contains no private user information.

Excelchat Expert
18/03/2018 - 02:36

Hi

User
18/03/2018 - 02:37

G'DAy im Steve How are ya mate?

Excelchat Expert
18/03/2018 - 02:37

you there?

Excelchat Expert
18/03/2018 - 02:37

Yes I am ok, you?

Excelchat Expert
18/03/2018 - 02:37

so do you have a file to work on?

User
18/03/2018 - 02:37

good ae you a M or F?

User
18/03/2018 - 02:37

yes one minute

Excelchat Expert
18/03/2018 - 02:37

thanks

User
18/03/2018 - 02:38

just the formula ill paste

Excelchat Expert
18/03/2018 - 02:39

G1?

User
18/03/2018 - 02:39

ok so this formula sums across the reference in column A but i only want it to sum where there is data not blanks

Excelchat Expert
18/03/2018 - 02:40

By sum I think you means count the entries, right?

User
18/03/2018 - 02:40

for the record A:A is a list of names ill add

User
18/03/2018 - 02:40

so i don't want to get any other sums but the count of the quantity of each individual name

User
18/03/2018 - 02:41

oh yes count

User
18/03/2018 - 02:41

basically i think in my real sheet the formula as pasted is counting the blank cells

User
18/03/2018 - 02:41

the blank cells up to A2000

User
18/03/2018 - 02:42

i think its counting the blank as a unique name also

Excelchat Expert
18/03/2018 - 02:42

check in G1

User
18/03/2018 - 02:42

i hope this is understandable

User
18/03/2018 - 02:42

ok ill check

User
18/03/2018 - 02:43

i don't want to count the blank cells

User
18/03/2018 - 02:43

that is what you have done

User
18/03/2018 - 02:43

i want to count the unique names as the formula did but not count the blank cells

User
18/03/2018 - 02:44

do you understand what i mean?

Excelchat Expert
18/03/2018 - 02:46

ok

User
18/03/2018 - 02:46

this formula is what i use to return the unique names

Excelchat Expert
18/03/2018 - 02:47

so there is repeatation also

User
18/03/2018 - 02:48

i have the formula to make a list of all the unique names that appear but i also want to count how many times each unique name appeared

User
18/03/2018 - 02:48

do you understand what i mean?

Excelchat Expert
18/03/2018 - 02:48

so if you have the list of unique names

Excelchat Expert
18/03/2018 - 02:49

then the formula I gave will work fine

User
18/03/2018 - 02:49

ok so i copied the formula that i use to generate the list of unique names. it is in F1

User
18/03/2018 - 02:49

it is not working because the references are probably different in this sheet

Excelchat Expert
18/03/2018 - 02:50

let me see

User
18/03/2018 - 02:50

i think it has to have array in front for google sheets

User
18/03/2018 - 02:50

I'm using excel

User
18/03/2018 - 02:52

do you get what i mean?

Excelchat Expert
18/03/2018 - 02:52

yes

Excelchat Expert
18/03/2018 - 02:52

Array is also available in googlesheet too

User
18/03/2018 - 02:52

i understand my formula in F1 is not right

User
18/03/2018 - 02:53

i copied what i used in excel then tried to fix for sheets

User
18/03/2018 - 02:53

F2 has my original formula from my excel

User
18/03/2018 - 02:53

it should return the list of unique names

Excelchat Expert
18/03/2018 - 02:54

ok let me see

User
18/03/2018 - 02:59

any luck?

Excelchat Expert
18/03/2018 - 03:01

here

[Uploaded an Excel file]

User
18/03/2018 - 03:03

ok ill check one moment

User
18/03/2018 - 03:03

what does that formula count?

User
18/03/2018 - 03:04

it should count how many times the unique names listed in column F appear in column A

User
18/03/2018 - 03:04

hello?

Excelchat Expert
18/03/2018 - 03:04

hi yes

Excelchat Expert
18/03/2018 - 03:05

h ok

Excelchat Expert
18/03/2018 - 03:05

there needs to be names in column F too?

User
18/03/2018 - 03:06

yes as i said the formula is there but needs fixing to suit this sheet

User
18/03/2018 - 03:06

the formula in column F lists the unique names in A:A

Excelchat Expert
18/03/2018 - 03:06

so write the names in col F then

Excelchat Expert
18/03/2018 - 03:06

those which needs to be checked

User
18/03/2018 - 03:07

no you don't understand

Excelchat Expert
18/03/2018 - 03:08

You have a set of names in F right?

Excelchat Expert
18/03/2018 - 03:08

based on what you need to check entries in A, right?

Excelchat Expert
18/03/2018 - 03:10

wait

Excelchat Expert
18/03/2018 - 03:10

My formula is giving exactly what is needed

Excelchat Expert
18/03/2018 - 03:10

you need the number of times they appear?

Excelchat Expert
18/03/2018 - 03:15

here

[Uploaded an Excel file]

Excelchat Expert
18/03/2018 - 03:15

ok now?

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