# How do I lookup a 'quantity' of items in excel?

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:

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.

-
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

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.

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

-
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:

``````=COUNTIF(\$Y\$4:\$Y\$15,AA4)
``````

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

``````=COUNTIF(\$Y\$4:\$Y\$15,AA4)
=COUNTIF(\$Y\$4:\$Y\$15,AA5)
=COUNTIF(\$Y\$4:\$Y\$15,AA6)
=COUNTIF(\$Y\$4:\$Y\$15,AA7)
=COUNTIF(\$Y\$4:\$Y\$15,AA8)
``````

Update:

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):

``````=SMALL(\$Y\$4:\$Y\$15,ROW(AA4)-3)
``````

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:

``````=COUNTIF(\$Y\$4:\$Y\$15,AA4)
``````

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.

-
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