Feb 08 2021 11:03 AM - edited Feb 08 2021 11:53 AM
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.
Feb 08 2021 11:55 AM
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.
Feb 09 2021 06:39 AM
Thank you but I could not figure out how to apply your formula to my data :0( @NikolinoDE
Mar 02 2023 02:14 PM
Mar 02 2023 04:13 PM - edited Mar 02 2023 04:14 PM
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;