Forum Discussion
Formula for Distinct Count with Multiple Criteria
I found a formula that helps me do a distinct count
=SUMPRODUCT((Data!$A:$A<>"")/COUNTIF(Data!$A:$A,Data!$A:$A&""))
However, now I need this to contain 2 criteria. I have similar formula but this one does not count distinct numbers
=IF((COUNTIFS(Data!$J:$J,A8,Data!$I:$I,'Staff Enc'!$B$4))=0,"",(COUNTIFS(Data!$J:$J,A8,Data!$I:$I,'Staff Enc'!$B$4)))
Does anyone have a suggestion?
I added a sample report of how the second formula works for me. I need something similar to give me a distinct out of the unique id.
4 Replies
how about sql？
select Date,Encounter,count(distinct(`unique id`)) cnt,group_concat(distinct(`unique id`)) from basic_to_two_dim_count_distinct group by Date,Encounter;
cli_create_two_dim~basic_to_two_dim_count_distinct~Encounter~unique id~count(distinct(`unique id`));
select * from basic_to_two_dim_count_distinct_two_dim order by Date COLLATE NATURAL_CMP;Examble with 3 Criterias
Shift + Ctrl + Enter keys together to get the unique result, see screenshot:
I would be happy to know if I could help.
Nikolino
I know I don't know anything (Socrates)
* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.
- This solution is relevant to me too. TYet, I have modified the illustrated formula by replacing the fixed value by cell reference, (i.e. in the example, by replacing "Tom" with F2 and "North" with G2 and entirely removed the criteria for date range as it's not applicable to my application). It still works.
Thank you but I could not figure out how to apply your formula to my data :0( NikolinoDE
