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
[Uploaded an Excel file]
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.
