Counting distinct entries that meet an additional criteria in another cell

Hi there,
I'm scratching my head trying to come up with a sheet summary folder that in the example below counts the number of distinct apps that meet a particular criteria in the type column.

To get the number of unique applications is straightforward =COUNT(DISTINCT(Application:Application))

but I also want to calculate the number of unique applications that are of Native type, and equally the number of unique applications that are of Web type. In the example below those answers should be 2 and 2 respectively.

Welcome any pointers, I'm hoping this is a simple issue but haven't been able to find an answer searching around the community answers.

Thanks,
Ken

Best Answer

  • Mark Safran
    Mark Safran Community Champion
    Answer ✓

    Hey @Ken Morse — you just need to add the COLLECT( ) function after your DISTINCT( ) call.

    For your Unique Native Applications, it would be this:

    =COUNT(DISTINCT(COLLECT(Application:Application, Type:Type, "Native")))

    For your Unique Web Applications, it would be this:

    =COUNT(DISTINCT(COLLECT(Application:Application, Type:Type, "Web")))

    -MCS

Answers