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

Question

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.

Answer 1

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)

And bob's your uncle.

---

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.