Array Formulas
How to Count the Items that Occur a Certain Number of Times in a List
In a list of items that could appear any number of times, here's
how to count the number of items that appear only once. Or
twice. And so on.
by
Charley Kyd, MBA Microsoft Excel MVP, 20052014
The Father of Spreadsheet Dashboard Reports

"I have a list of repair orders (RO). I want to know how many one line repair orders there
are in the list. So if a repair order number is repeated I do not want to count that repair order number
at all. Only the repair orders that are listed once. I’ve been going nuts trying to figure this out."  Tom C.

Let's start with a quick example. Column A below has a list of 8 ROs. Some appear in the list only once and some appear several times.
You want to know how many have appeared only once.
Notice that this is a different question than the
FREQUENCEY
function answers. That function would count how often specific ROs occur in a list. But here, you want to know
how many ROs appear a given number of times.
In your case, you're only interested in a number of times
equal to 1, but you could answer this question for any number of
times an RO occurs, as I show in this example.
I can think of two ways to do this; both rely on arrays.
Version 1
In the Version 1 column, here's the formula for the cell shown:
D4: {=SUM(IF(COUNTIF(RO,RO)=$C4,1,0))}
Notice that this is an array formula. You type the formula as
you normally would, but rather than pressing Enter to enter it, you hold
down Ctrl and Shift, then press Enter. When you do so, the
braces automatically surround the formula as shown.
The key part of this formula is the
COUNTIF function, which
has this syntax:
=COUNTIF(range, criteria)
Notice in the formula in cell D4 that I've used RO as the
argument for both the range and the criteria. Here's how this
works...
When you enter the function as an array, it's going to return
an array of results for each criteria we specify. So because we
specified a range of eight cells for our criteria, it's going to return an array
of eight results.
For each of those eight criteria, Excel performs the COUNTIF
test on the range that we've specified, and then returns each
result to the array.
To make sure this part of the formula was doing what I
expected, I selected just the COUNTIF(RO,RO)
part of the formla in my formula bar, then pressed F9. When I
did so, I got this result:
Working from left to right in the highlighted area, it tells
us that the first item in the list appeared once; the second
item in the list appeared twice; the third item appeared three
times; the fourth item appeared twice; and so on.
After I saw that the function was doing what I expected, I
pressed Esc to return to my original formula.
In the figure, it's easy to see how the rest of the formula
works. The IF
function returns an array of ones and zeros. That array contains
a value of 1 when a value in the highlighted array equals the
value in cell C4, which is 1 in this case; otherwise, it returns
zero. Then, finally, the
SUM function adds up those ones.
Version 2
In the Version 2 column, here's the first formula:
E4: =SUMPRODUCT((COUNTIF(RO,RO)=$C4)+0)
This formula is slightly easier to use, because we don't have
to enter it as an array. This is because
SUMPRODUCT is
a unique function that's usually entered normally, but
automatically acts as though it were entered as an array.
Here, however, the section (COUNTIF(RO,RO)=$C4)
returns an array of TRUE or FALSE values. But because Excel
doesn't recognize TRUE as a value in a sum, we must add zero (or
multiply by 1) to convert those TRUE and FALSE values to 1s and
0s. Then the SUMPRODUCT returns the results.
Warning
These formulas are doing a LOT of work. And the amount of
work increases by the square of the number of rows in your RO
range. So if your number of repair orders grows to many
thousands, expect this calculation to take some time.
The Other Formulas in the Example
Just
to complete the picture, here are two other formulas used in the
figure, repeated here.
A10: =COUNT(RO)
This formula uses the
COUNT function to
return the number of items in the list.
D8: =SUM(D4:D7)
E8: =SUM(E4:E7)
These formulas return the sum of the counts in columns D and
E. Because these values both equal the total number of items,
they gives us a pretty good idea that our formulas are doing
what we expect.
