hi,
does anyone know how to count number of types of cells in a column.
for example, below column has 3 types of cells:
xxx
yyy
zzz
xxx
xxx
zzz
yyy
many thanks!!
hi,
does anyone know how to count number of types of cells in a column.
for example, below column has 3 types of cells:
xxx
yyy
zzz
xxx
xxx
zzz
yyy
many thanks!!
Last edited by afgi; 07-05-2011 at 12:33 PM.
Hi afgi,
I'd be looking at delete duplicates for an answer. If you were to copy that column to a blank spot and delete duplicates on the copied column, it would reduce it to unique values. You could easily count how many were left.
Is that what you wanted?
One test is worth a thousand opinions.
Click the * Add Reputation below to say thanks.
This formula will count only different non-blank entries in A2:A100
=SUMPRODUCT((A2:A100<>"")/COUNTIF(A2:A100,A2:A100&""))
change range as required
Audere est facere
@ DLL,
Can you explain in words how this formula works?
Hi,
I'm using "countif"
i trust you'll have a summary section that will have
XXX =
YYY=
etc.... if you do then insert the =COUNTIF(H16:H498,"YYY") or XXX or ......
hi,
dear MarvinP , I cannot remove data, because I need it. I need to find a way to get this result in pivot table.
So, I have a question to daddylonglegs. Are you able to put your formula in excel and attache it as a file in this thread?
many thanks
Yes......
...assume that the sample data that afgi provided is in A2:A8, i.e.
xxx
yyy
zzz
xxx
xxx
zzz
yyy
Then this basic formula
=COUNTIF(A2:A8,A2:A8)
returns an array like this
={3;2;2;3;3;2;2}
Note that there is a 3 in every xxx position because there are 3 of those, and a 2 in every yyy or zzz position because there are 2 each of those.
Now because there will always be three 3s (or four 4s....or five 5s, or 6 sixes) then if you divide 1 by that array and sum the results you get the count of different values, i.e. if I use this
=1/COUNTIF(A2:A8,A2:A8)
that gives me
={1/3;1/2;1/2;1/3;1/3;1/2;1/2}
so
=SUMPRODUCT(1/COUNTIF(A2:A8,A2:A8))=3 as required
The extra parts are to avoid errors. If A8 is blank then the COUNTIF part returns
={3;1;2;3;3;2;0}
and when you divide 1 by that array the zero leads to a #DIV/0! error.......so it turns out that making the sceond argument into text avoids that, i.e.
=COUNTIF(A2:A8,A2:A8&"")
gives you
{3;1;2;3;3;2;1}
...so that avoids #DIV/0! error but now
=SUMPRODUCT(1/COUNTIF(A2:A8,A2:A8&""))=4
because the blank is being counted......so to get 3 you can add another array to the SUMPRODUCT to filter out blanks, i.e.
=SUMPRODUCT((A2:A8<>"")+0,1/COUNTIF(A2:A8,A2:A8&""))
which now gives 3 as expected
....and you can shorten that to this
=SUMPRODUCT((A2:A8<>"")/COUNTIF(A2:A8,A2:A8&""))
see attached
Dear daddylonglegs, works great. MANY THANKS)
Dear daddylonglegs, thank you for providing the formula, it really helpful
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks