“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’occurrencesve 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 formula 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.
