How to count by unique id values that contain specific text in GoogleSpreadsheet

For this question, I understand that SQL could be used via =query("some code"), it is completely valid this way, but if a simpler way exists, better.

I want to count in GoogleSpreadsheet all the disabled accounts, but only once per account_id

account_status  account_id
disabled: 22/02/2021    3
disabled: 15/02/2021    3
disabled: 17/02/2021    4
disabled: 22/02/2021    4
active: 22/02/2021      5
active: 22/02/2021      5
active: 04/02/2021      6
active: 22/02/2021      6
active: 22/02/2021      7
active: 22/02/2021      7
active: 05/02/2021      7
active: 22/02/2021      8 
active: 22/02/2021      8
active: 22/02/2021      9
active: 22/02/2021      9

In this specific case, I have 2 accounts with id 4 and 3 respectively, in total, 4 row, but I want the result of the count to be the number of accounts disabled, in this case 2

I tried unsuccessfully:

=COUNTIFS('mysheet'!S2:S, "active")
=COUNTUNIQUEIFS('mysheet'!S2:S, 'mysheet'!C2:C,"disabled")

Answer

try this:

=COUNTUNIQUEIFS(C2:C; A2:A; "disabled*")

works even if A column contains a date

Advertisement

Answer