# Count unique references

I have reference numbers in column `H` and status of repair in column `R` (yes or no).

I am trying to count the unique reference numbers in column `H` only if column `R` is `No`.

I currently have the formula `=SUM(- -(Frequency(H2:H58,H2:H58)>0))`, which provides me with a count of the unique reference number. However, can you advise me how to update this to count unique reference numbers in `H2:H58` only if `R2:R58 = No`

• A good phrase to Google for this would be "Excel Count unique numeric values with criteria". Lots of resources available explaining how to do this. Enjoy. – Alex M Feb 13 at 20:19
• Thank you. I have spent some time looking at this but not having much luck, hopefully someone will be able to help with a suitable formula – Amc Feb 13 at 20:21

You just need to look at only the data that meet your criteria.

``````=SUM(--(FREQUENCY(IF(B1:B8="No",A1:A8,""),IF(B1:B8="No",A1:A8,""))>0))
``````

This is an array formula. In some versions of Excel, to enter/confirm an array formula, hold down ctrl + shift while hitting enter. If you do this correctly, Excel will place braces `{...}` around the formula seen in the formula bar.

• Thank you, can you advise me how I should complete the formula above after “No” I can’t see the end of the formula. Thank you – Amc Feb 15 at 10:46
• @Amc There should be a scroll bar below the formula to allow you to see the rest of it. And you should be able to select/copy/paste the formula. However, the part after "No" is "`,A1:A8,""))>0))`" without the surrounding double quotes `"` – Ron Rosenfeld Feb 15 at 13:20
• Thank you so much, it worked for me. – Amc Feb 15 at 13:27
• Glad to help. Since my answer seems to be what you require, kindly mark it as Accepted. See What should I do when someone answers my question. – Ron Rosenfeld Feb 15 at 14:00

With Office 365 subscription one can use the Dynamic Array formulas:

``````=COUNTA(UNIQUE(FILTER(H:H,R:R="No")))
`````` • Thank you, I gave this a try and received message this function isn’t valid, do you have any other thoughts please? – Amc Feb 13 at 20:30
• isn't valid? or you get `#NAME` error? – Scott Craner Feb 13 at 20:36
• The response isn’t valid – Amc Feb 13 at 20:57
• interesting, as you can see by my edit, it works for me. I would expect the `#NAME` error if you did not have the dynamic array formula. But for it not be a valid input, would be something else. Does your local setting require `;` instead of `,` for the delimiter? – Scott Craner Feb 13 at 21:21
• It doesn’t seem to like filter – Amc Feb 13 at 21:26