Count distinct values based on other cell criteria

Ruth Schoonover

I have been able to figure out how to count distinct values in a column. Now I would like an additional complexity of asking the formula only to count cells if it meets the criteria in another column.

If the value in the Status column is 'Live', count the distinct number values in the UniqueDiseaseID column.

=COUNTIF(Status2:Status1083, "Live") COUNT(DISTINCT(UniqueChapterID2:UniqueChapterID1083))

I got this far, I am sure syntax is a mess.

Thanks for your help!

Darren Mullen

@Ruth Schoonover You'll need to use an if statement to check the value of the status column.. but the bigger question is... where are you wanting to put the result? *I THINK* you want to check the value of the status column on a given row, then put the result of the count distinct into a cell in this row?

If so.. then you would simple use if( Status@row = "Live", COUNT(DISTINCT(UniqueChapterID2:UniqueChapterID1083)), "")

Ruth Schoonover

I have this successfully counting the distinct Unique Chapter IDs

=COUNT(DISTINCT(UniqueChapterID:UniqueChapterID))

Now I only want it to count the Unique Chapter ID if the value in the Status column is 'Live'

Ruth Schoonover

I want the result/answer to be placed in the cell where I have the formula.