Excel: How to count number of distinct values that meet multiple conditions

Client Number	Matter Number	Address	Physical Files	Electronic Files	If any?
10001	1000101	Addy1	No	No	0
10001	1000102	\|\|	No	No	0
10002	1000201	NULL			1
10003	1000301	Addy3	Yes	Yes	1
10004	1000401	Addy4	No	No	0
10004	1000402	\|\|	Yes	No	1
10004	1000403	\|\|	No	No	0
10005	1000501	Addy5	No	Yes	1
10006	1000601	Addy6	No	No	0

I have a large excel sheet that for all intents and purposes looks like this example ^^

The "If any?" column has this formula: =IF(AND('Physical Files'="No", 'Electronic Files'="No"),0,1)

I'm trying to count the number of clients that have no physical or electronic files. Each client has a distinct Client Number, but some appear multiple times with multiple matters. If there is a client address on file, then it is noted as well as whether there are physical and/or electronic files. If there is no address, the Physical/Electronic columns are left blank.

Distinct Client #s	Files?
10001	0
10002	1
10003	1
10004	1
10005	1
10006	0

Right now I have a generated list of distinct client #s using =UNIQUE(). In the neighboring column I have this formula: =SUM(FILTER(M2:M10, H2:H10=O2)) --> Column M = "If any?" and Column H = "Client Number" in the example sheet, and Column O = "Distinct Client #s".

From that, I've used a COUNTIF for when the "Files?" Column = 0 -- the result is 2, which is correct, but I'm trying to find a way to get that result without having to make a list of the distinct client numbers. Is there a way to do this in a single cell?

CodePudding user response：

You can try the following in cell H2:

=HSTACK(UNIQUE(A2:A10), SUMIFS(F2:F10,A2:A10,UNIQUE(A2:A10)))

and here is the output:

If you don't have HSTACK available in your excel version, then you can use the following:

=CHOOSE({1,2}, UNIQUE(A2:A10), SUMIFS(F2:F10,A2:A10,UNIQUE(A2:A10)))

CodePudding user response：

=SUMPRODUCT(        
            (MMULT(               
                   COUNTIFS(A2:A10,A2:A10,D2:D10,{"Yes",""}) COUNTIFS(A2:A10,A2:A10,E2:E10,{"Yes",""}),
                   {1;1})
                         >0)
             /COUNTIF(A2:A10,A2:A10)
             )

MMULT calculates which ID's count one or more matches of Yes or "" in column D or E divided by the count of the ID. So if you SUM those it will sum to 1 per ID, so the result of the formula is the count of unique ID's meeting given conditions.

Edit: I read it's the other way around; then subtract the previous from the total unique count:

=SUM(1/COUNTIF(A2:A10,A2:A10))-
     SUMPRODUCT(
                (MMULT(
     
                       COUNTIFS(A2:A10,A2:A10,D2:D10,{"Yes",""}) COUNTIFS(A2:A10,A2:A10,E2:E10,{"Yes",""}),
                       {1;1})
                             >0)
       
                /COUNTIF(A2:A10,A2:A10)
                )