1

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
2

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

Adjust the references to account for where your data is on your worksheet.

=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
3

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

=COUNTA(UNIQUE(FILTER(H:H,R:R="No")))

enter image description here

  • 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.