forumula for unique values and countif combined

[bkinlic]

Hello, Please advise me as I am just learning how to use these functions.

I am trying to use a formula that counts unique text values from this rangePatient_level!C6:C15000)
while meeting certain criteria ('yes') in this range: Patient_level!I6:I1048576.

I have working formulas for both (separately) but can't figure out how to combine them to sum into one cell..- not sure if I use sum or countif...

My formula for counting unique values:
=SUM(IF(FREQUENCY(IF(LEN(Patient_level!C6:C15000)>0,MATCH(Patient_level!C6:C15000,Patient_level!C6:C15000,0),""), IF(LEN(Patient_level!C6:C15000)>0,MATCH(Patient_level!C6:C15000,Patient_level!C6:C15000,0),""))>0,1))

And my formula for meeting criteria 'Yes':
=COUNTIF(Patient_level!I6:I1048576,"Yes")

Thank you in advance!!!
-B

 

[bkinlic]

Sorry if there is a simple fix to this..Im still learning

 

[FDibbins]

To begin with, you need to use the same size ranges - your countif() is using pretty much an entire column.

Then, I would try to add that into the 1st part of your formula as a test, not a countif...

=SUM(IF(FREQUENCY(IF(LEN(Patient_level!C6:C15000)>0,IF(Patient_level!I6:I15000="Yes",MATCH(Patient_level!C6:C15000,Patient_level!C6:C15000,0),"")), IF(LEN(Patient_level!C6:C15000)>0,MATCH(Patient_level!C6:C15000,Patient_level!C6:C15000,0),""))>0,1))

Untested

 

[bkinlic]

This works as an array.
Thank you very much Ford!

 

[FDibbins]

Glad I could help