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.
