# Excel - COLUMN Function Problem - Expert Solution

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

In column A I have 1500 lines of data, some of these are valued at zero, I need to write a formula which removes the values of 0 and then gives me a sum of the unique names in column b. I am aware I could filter column a to remove "0's" and then remove duplicates from the remaining cells in column B, but this must be done in a formula
Solved by O. Q. in 56 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 27/08/2018 - 04:18
Hello, I understand that you would like to sum all the unique values in column B, right?
User 27/08/2018 - 04:18
not quite
Excelchat Expert 27/08/2018 - 04:18
Are you able to share a file or an accurate sample of it so I can understand it better?
User 27/08/2018 - 04:18
i need to first remove the values of zero from column a, then find the unique values in column b
Excelchat Expert 27/08/2018 - 04:19
Okay, what I need to know is why 0 values will need to be removed when even if you include them, the sum should not affected anyway.
Excelchat Expert 27/08/2018 - 04:20
I mean, even if you have a thousand 0's in there and include them in the SUM, the result will still be the same, right? Unless of course you can show me how it'll affect the result by showing an example.
User 27/08/2018 - 04:21
i do not need them
Excelchat Expert 27/08/2018 - 04:21
Okay, then are you able to share your file or at least a sample so I can provide an accurate solution?
User 27/08/2018 - 04:21
i have attached an example
Excelchat Expert 27/08/2018 - 04:22
Thank you. Please give me a few minutes.
Excelchat Expert 27/08/2018 - 04:22
Before we proceed, this is a reminder that our policy is 1 question per session so for this session we'll be resolving this particular question. We also currently do not support VBA/Macro solutions.
Excelchat Expert 27/08/2018 - 04:24
When you say unique, only add if the name is mentioned once or only add the first time the name is mentioned?
User 27/08/2018 - 04:24
its ok, the only question is how could i link a formula which is removing the 0's from column a (=countif(A:A,">0") to then only show the unique values that would remain in column b
User 27/08/2018 - 04:24
the first time the name is mentioned
User 27/08/2018 - 04:25
each unique name in column b that has a value greater than 0 in column a
Excelchat Expert 27/08/2018 - 04:25
Okay, are you open to adding a helper column? That will make things a lot simpler.
User 27/08/2018 - 04:25
whats that
Excelchat Expert 27/08/2018 - 04:25
It's adding a formula in a new column that will help with the SUMIFS() formula we'll create.
User 27/08/2018 - 04:26
hmm, not ideal
User 27/08/2018 - 04:26
i need this in one formula
User 27/08/2018 - 04:26
if i need to manipulate the static data then i just as well just filter column a to remove 0's
Excelchat Expert 27/08/2018 - 04:26
Alright, I'm not sure I can create that especially with the time limit we have but let me give it a shot.
User 27/08/2018 - 04:27
then do the formula to get unique values
Excelchat Expert 27/08/2018 - 04:27
That's also another option, to create a formula that will first create a unique list of the names and then use SUMIFS based on that.
Excelchat Expert 27/08/2018 - 04:28
I'm not confident that it is possible to create all this in one cell though as even the one that uses a unique list require an array formula already.
User 27/08/2018 - 04:28
i know the formulas to do both parts
User 27/08/2018 - 04:28
just not hwo to combine it
User 27/08/2018 - 04:29
so, remove 0's: =countif(A:A,">0")
Excelchat Expert 27/08/2018 - 04:29
I can do both parts as well but like what I'm saying, combining them is the real challenge because we are already working with an array formula for one part.
Excelchat Expert 27/08/2018 - 04:29
And that alone is very difficult to incorporate in one formula.
Excelchat Expert 27/08/2018 - 04:29
The formula to create the unique list will require multiple cells to be possible.
User 27/08/2018 - 04:30
number of unique names; =sumproduct(1/countif(b:b,b:b))
Excelchat Expert 27/08/2018 - 04:30
So if you want that formula in one cell alone, I'm not even sure it is possible.
Excelchat Expert 27/08/2018 - 04:30
But, like what I said, I'm going to give it a shot.
Excelchat Expert 27/08/2018 - 04:31
A few more minutes, please extend the session once you are prompted.
Excelchat Expert 27/08/2018 - 04:35
So here's what I currently have:
Excelchat Expert 27/08/2018 - 04:35
{=SUM(IF((COUNTIFS(F1:F6,F1:F6)=1)*(E1:E6<>0),E1:E6,FALSE))}
Excelchat Expert 27/08/2018 - 04:35
This sums all the UNIQUE values and not 0 values.
Excelchat Expert 27/08/2018 - 04:35
The problem is that this only sums the truly unique values.
Excelchat Expert 27/08/2018 - 04:35
So if there are 2 of the same names, then it won't add them because they are no longer unique.
Excelchat Expert 27/08/2018 - 04:36
But since you mentioned that it will have to sum the first instance, this is where I'm getting stuck and so many others.
Excelchat Expert 27/08/2018 - 04:37
The formula to grab the first instance will seem to require a previous list to be referenced so it doesn't look like we'll be able to create this in one cell.
Excelchat Expert 27/08/2018 - 04:37
Hi, it seems that you have not extend the session.
Excelchat Expert 27/08/2018 - 04:37
Our session will expire and there's nothing I can do to stop it.
Excelchat Expert 27/08/2018 - 05:01
I created a formula in columns D and E in the document preview to show you why it won't be possible to incorporate everything in a sinle formula in a single cell.
Excelchat Expert 27/08/2018 - 05:02
If you'll look at the formula in column D, one part of the formula is what we call an Expanding range formula.
Excelchat Expert 27/08/2018 - 05:02
COUNTIF(\$D\$4:D4,\$B\$1:\$B\$89)
Excelchat Expert 27/08/2018 - 05:03
This part is what we call an expanding range formula, the reason for that name is because if you'll look closely, only the first part of D4:D4 is locked.
Excelchat Expert 27/08/2018 - 05:03
This means that when this formula is applied to the next cell, only the second part of D4:D4 will change.
Excelchat Expert 27/08/2018 - 05:03
So the next formula will be D4:D5
Excelchat Expert 27/08/2018 - 05:03
Then D4:D6.
Excelchat Expert 27/08/2018 - 05:03
This is very important in order to generate a list of unique names in a list.
Excelchat Expert 27/08/2018 - 05:04
Without this part, you won't be able to generate a unique list. There may be other ways but it will require multiple cells as well.
Excelchat Expert 27/08/2018 - 05:04
Now, this is the one major hurdle why we can't create this formula in one cell.
Excelchat Expert 27/08/2018 - 05:10
Honestly, If I'd known that you are trying to add the first occurence and the formula being in one cell only then I would've skipped this session as that would make it not possible at all.
Excelchat Expert 27/08/2018 - 05:11
Now if we solve this based on your original description of adding UNIQUE names that aren't zero then the formula in I7 would be the solution.
Excelchat Expert 27/08/2018 - 05:12
I'll leave it up to you to decide if you want to use helper columns and to create a unique list or not.
Excelchat Expert 27/08/2018 - 05:12
But to be sure, it is not going to be possible to have all these logic in one cell as generating the Unique list will require an expanding range.
Excelchat Expert 27/08/2018 - 05:13
I'll be marking this session as Done since the original requirement of UNIQUE names that aren't 0 is in I7 cell.

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.