LinkBack URL
About LinkBacks
Is there a simple formula that counts unique values in a column? I have tried the following on one column and it worked but it did not work for other columns. I received the "#DIV/0!" error
=+SUMPRODUCT(1/COUNTIF(D4:D402, D4:D402))
Is there a simple formula that counts unique values in a column? I have tried the following on one column and it worked but it did not work for other columns. I received the "#DIV/0!" error
=+SUMPRODUCT(1/COUNTIF(D4:D402, D4:D402))
Last edited by alansidman; 04-02-2019 at 07:40 PM.
in addition, i would like the formula to adjust as i add or remove values.
Perhaps the following will help.
The following array entered formula* will list the distinct numbers: =INDIRECT(TEXT(MIN(IF((Table1<>"")*(COUNTIF(G$3:G3,Table1)=0),ROW(Table1)*100+COLUMN(Table1),6553601)),"R0C00"),0)&""
*Array entered formulas are confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
The following formula will count the distinct numbers in the list: =COUNTIFS(G4:G1000,"<>")
Note that the columns containing the numbers are transformed into a table so that numbers may be added without changing the formula.
Note that the column of distinct numbers may be moved and/or hidden for aesthetic purposes.
Let us know if you have any questions.
Last edited by JeteMc; 04-02-2019 at 08:36 AM.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
Is this the type of sort you are requiring.
In a way yes, but I want to count them numerically, not pull them out as a word. Also, I am counting based on merged cells. The merged cells also were formatted in such a way that I want the ability to filter off the merged cell rather than just cell Q3 in the example below. To create the merged cell I had to identify each row I was using for "NEOLITH" and then I copied and pasted those four cells into a neighboring cells. I then merged those four cells and than copied and did a paste special to keep the merged format the same back into the original cells.
Q3: NEOLITH
Q4: NEOLITH
Q5: NEOLITH
Q6: NEOLITH
I have gotten this formula to work but it doesn't exclude the blank cells now.
Please Login or Register to view this content.
Sorry, I will have to pass on assisting on this one.
One has learnt by experience NEVER data sort on merged cells.
It appears to work sometimes, then when your business is relying on the accuracy of the analysis 'BOOM' !
I am tooooo old to experience the learning curve again.
torachan
I suggest that you upload a small desensitized sample workbook (not a picture or pasted copy) by clicking on the GO ADVANCED button below the Quick Reply window and then scrolling down to Manage Attachments to open the upload window. Be sure to manually include the expected results so that we have something against which to test formulas/code.
Let us know if you have any questions.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks