Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Let's say I have a quantity of items:

1 2 3 4 5 4 3 2 1 2 3 4

in a column of cells. What I want to be able to do is count the quantity how many unique "items" there are in this array:

1 --> 2

2 --> 3

3 --> 3

4 ..> 3

And so forth. I want the table to look like this:

enter image description here

Also, is there a way to accomplish this if I don't know all of the values of the array to begin with? I'm looking for a way to have excel search an array, find a unique value, count how many times that value is in the array, and then move onto the next values.

share|improve this question
Have a look at COUNTA() and COUNTIF(). – user3463 Mar 18 '11 at 22:59
@RandolphPotter that appears to only count "non-blank" value... I want to be able to count all matching values – KronoS Mar 18 '11 at 23:00
I updated with COUNTIF(), which Matt has expanded on (I don't have Excel in front of me). – user3463 Mar 18 '11 at 23:04
up vote 1 down vote accepted

The best way to do this (for ease of setup and refreshing) is to use a pivot table. Set the pivot data to your column of values (with header) and set up your table as shown below.

enter image description here

The pivot table will automatically identify the unique values and provide counts for them in one step.

share|improve this answer
I didn't know about pivot tables before asking this question. This would be my preferred method now. – KronoS Jun 3 '13 at 15:41
@KronoS Thanks for the check mark! I was just adding this answer for reference since it's a pretty common user need in Excel. – Excellll Jun 3 '13 at 16:18

For that you want the COUNTIF() function. It counts the number of entries in a range that match a criteria.

Say your range is Y4 to Y15, and your individual items are AA4 to AA8 then AB4 would be:


And then copy and paste that to AB5, AB6, AB7, etc so it ends up with:


And bob's your uncle.


Here is a new system that better fits your needs, but still isn't quite there.

First we need to sort the list, so in the column next to the list of temperatures (or somewhere convenient anyway) you need to have repeated entries of the following formula (I will take column AA for this):


This will find the Nth smallest number in the range - the Nth being the row number of the current cell - 3 (so AA4 becomes 1). Repeating this down the page gives AA5 (2), AA6 (3), etc.

Now we can do the same count as before, but using the new numbers:


And repeating down the list.

The output should be something like:

1  1  2
2  1  2
3  2  3
4  2  3
5  2  3
4  3  3
3  3  3
2  3  3
1  4  3
2  4  3
3  4  3
4  5  1

Now if only we could find some way of reducing it to only display each number once.

share|improve this answer
Ah works great... One more further questions though? What if the data is randomized? I.E. I don't know all the value within the array. Is there a way to have excel search for a "unique" value, then count how many there are of that value, and then continue on for the next "unique" value, until the array has reached it's end? – KronoS Mar 18 '11 at 23:05
@KronoS you could use a pivot table to identify a list of unique values – Xantec Mar 18 '11 at 23:24
@Kronos I'd suggest to keep this topic only for the first question. Besides the fact that we have other posts telling how to take unique values, is more valuable to the community to have the question properly answered. Think of other people that may have a question similar to your second question: they'll never reach this topic, since it's not related to unique values, but to the way some data is handled. – Tiago Cardoso Mar 18 '11 at 23:30
@Kronos Actually, a pivot table can do it in a few clicks. – Majenko Mar 18 '11 at 23:41

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .