Forum Discussion
Count unique values among duplicates with 1 condition
Hi expertise,
I have a problem which is to count the unique job number under ticket type "C" as below. Answer should be "TWO" due to Job Number 3 & 4 both are under Ticket Type C.
I knew the formula to count the unique values among duplicate but I dont know how to create the formula for this case. Please help.
| Job Number | Ticket Type |
| 1 | A |
| 2 | B |
| 2 | B |
| 3 | C |
| 3 | C |
| 3 | C |
| 4 | C |
| 4 | C |
| 4 | C |
| 4 | C |
You can also try the following formula if you have Office 365...
=COUNT(UNIQUE(FILTER(A2:A11,B2:B11="C")))
5 Replies
You can also try the following formula if you have Office 365...
=COUNT(UNIQUE(FILTER(A2:A11,B2:B11="C")))thank you so much !
I got the answer by using your formulathanks for great helps
You're welcome Lawrence_Lam_320! Glad it worked for you.
Please take a minute to accept the post with the proposed answer as the Best Response to mark your question as Solved,
=SUMPRODUCT(IFERROR(1/(COUNTIFS(A2:A11,A2:A11,B2:B11,"=C")),0))You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.
thanks for your great help
