Using the UNIQUE and COUNTIFS function to count cells matching multiple criteria

New Contributor

Hello there,


I am struggling in finding a way to use the COUNTIFS function to count the number of cells (ASVs) that are unique and are classified as "active". Below is my best attempt thus far which returns the column of cells highlighted in light blue:


=COUNTIFS(D2:D24, UNIQUE(D2:D24), E2:E24, "=active")


Screen Shot 2022-06-14 at 4.33.05 PM.png


Any help or guidance would be appreciated!


2 Replies
best response confirmed by r2-Molin (New Contributor)



If you don't mind applying COUNTA instead of COUNTIFS you can try this formula.