Count distinct but SUCCESS clients. Part 2

[AlexWest]

Hi guys!

Yesterday I've made a post about distinct count and I've found an answer if there is 1 condition.

But now I have the same question but there are 2 conditions. And I tried everything and couldn't solve the task.

Well, I have an Excel table

The task is to get only those IDs that was Failed and never was SUCCESS and have a Fail_Reason.

I tried to count with E function, but there was only 1 condition. Every try to count with 2 conditions I got wrong numbers.

If you can, please help))

Thanks a lot!

[AlexWest]

You might be missed my description in my previous message, because I chaned it after posting.

If we use filters in Excel file, we will have the right result.

Filter 1. Sort by reg_Status; Remove Client ID doubles. Result - 8804 UNIQUE Clients.

Filter 2. in reg_Status exclude all Failed. Result - 4619 SUCCESSED Clients.

Filter 3. in reg_Status exclude all SUCCESS. Result - 4185 Failed Clients.

Filter 4. With filtered by Failed clients, exclude all blank (or 'None') Fail_Reason. Result - 2159 Denied Clients.

Filter 5. With filtered by Failed clients, exclude all not blank (or not 'None') Fail_Reason. Result - 2026 Clients who didn't finnish the Registration.

-----------------------------

Now, In my case, I already got Filter 3 (4185 Failed Clients), using your formula

Count(DISTINCT {<[Client ID]=E({<[reg_Status]-={'Failed'}>})*P({<[reg_Status]={'Failed'},[Fail_Reason]={"*"}>})>} [Client ID])

but I can't devide them on Denied and Unfinnished Clients.

[rubenmarin]

But I asked for some ClientID that are wrongly counted with the formula. It's beeter to go to specific examples than just go with gross values.

[AlexWest]

no, the data that I sent is real data. CliendID changed from 16-digit number to 1,2,3 etc. that's it.

My task is also real task, that I have to decide)

Or I understand you wrong?)

[rubenmarin]

I was asking to detect an example of ClientID that is wrongly counted by the expression, to work in that example and adapt the formula to that case