Count unique values meeting certain criteria

[Shree1]

Hi,

I have the below two columns. I want to count the unique names that come up as Priority 3.


I tried =SUMPRODUCT((B2:B5<>"")*(A2:A5="Priority 3")/COUNTIF(B2:B5,B2:B5)). However, it's giving me an incorrect number. Can someone help, please

Priority	Name
Priority 1	Sam
Priority 1	Tom
Priority 2	Dan
Priority 3	Adrian
Priority 3	Adrian

<TBODY>
</TBODY>

 

[Marcelo Branco]

Try this array formula
=SUM(IF(FREQUENCY(IF(A2:A6="Priority 3",IF(B2:B6<>"",MATCH(B2:B6,B2:B6,0))),ROW(B2:B6)-ROW(B2)+1),1))

confirmed with Ctrl+Shift+Enter, not just Enter

Hope this helps

M.

 

[Shree1]

Thanks for the speedy response, but I tried that and I got #N/A

 

[Shree1]

Actually, it works if I have it on the same worksheet, I had a table in a separate worksheet and was writing the formula there, whilst poiting to the cell references on the original sheet

 

[Marcelo Branco]

Actually, it works if I have it on the same worksheet, I had a table in a separate worksheet and was writing the formula there, whilst poiting to the cell references on the original sheet

Adjust the cell references and the formula should work.

M.