Complicated COUNTIF

[megera716]

Complicated for me anyway

I have a range with ~18,000 rows called Clients and another range with 200 rows called Names where I'm trying to calculate.

I put a COUNTIFS in Column C that counts the records that contain Name A, B or C AND Location 1.
C3:

=COUNTIFS(Clients!$C$3:$C$18261,$A3,Clients!$A$3:$A$18261,$B3)

There are 143 clients for Location 1, and the three people assigned to this location account for 124 of them. The other 19 are assigned to one of several names but I don't care about those.

Basically, what formula can I put in the Names range to count all the records in Clients that have any name that is assigned to Location 1 in the Names range (there might be just 3 as in my over-simplified example or there might be 37)? I will be working with the same 40ish locations every month but the Names assigned to a Location can and do change month to month so it has to be based on what is in Names.

 

[alansidman]

With Power query

let
    Source2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Grouped Rows" = Table.Group(Source2, {"Location", "Name"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Grouped Rows",{"Name", "Location", "Count"}),
    #"Sorted Rows" = Table.Sort(#"Reordered Columns",{{"Name", Order.Ascending}, {"Location", Order.Ascending}})
in
    #"Sorted Rows"

Book7
	A	B	C	D	E	F	G	H	I	J
1		Table1			Table2				Result
2	Names	Assigned to Location		Location	Client	Name		Name	Location	Count
3	A	1		1	Client11	E		A	1	1
4	B	1		1	Client35	B		A	3	1
5	C	1		1	Client36	B		B	1	2
6	D	2		1	Client83	C		C	1	1
7	E	3		1	Client15	A		C	2	1
8				2	Client86	C		D	2	1
9				2	Client10	D		E	1	1
10				3	Client29	A
11
Sheet1

 

[megera716]

Oh boy, looks like I'm going to need to educate myself on PowerQuery

 

[Eric W]

Or with a formula:

Book2
	A	B	C	D	E	F	G	H	I	J
1		Table1			Table2				Result
2	Names	Assigned to Location		Location	Client	Name		Name	Location	Count
3	A	1		1	Client11	E		A	1	1
4	B	1		1	Client35	B		A	3	1
5	C	1		1	Client36	B		B	1	2
6	D	2		1	Client83	C		C	1	1
7	E	3		1	Client15	A		C	2	1
8				2	Client86	C		D	2	1
9				2	Client10	D		E	1	1
10				3	Client29	A
11
Sheet1

Cell Formulas
Range	Formula
H3:J9	H3	=LET(names,A3:A10,tbl,D3:F20,loc,INDEX(tbl,0,1),name,INDEX(tbl,0,3),f,FILTER(tbl,(name<>"")*COUNTIF(names,name)),u,SORT(UNIQUE(CHOOSECOLS(f,3,1))),c,COUNTIFS(name,INDEX(u,0,1),loc,INDEX(u,0,2)),HSTACK(u,c))
Dynamic array formulas.

 

[Holger]

or maybe something like this:

Let Function - Map^J byRow^J Reduce^J Sum ^0 Count ^0 SumProduct solution for same problem - coersed array conditions.xlsx
	BA	BB	BC
8	Names	Location	# of Clients
9	A	1	4
10	B	1	4
11	C	1	4
12	D	2	1
13	E	3	1
14	F	1	4
15
16
17
18
19
20
21
22
23
24	Location	Client	Name
25	1	Client11	E
26	1	Client35	B
27	1	Client46	B
28	1	Client23	C
29	1	Client49	A
30	2	Client12	C
31	3	Client90	D
Sheet1

Cell Formulas
Range	Formula
BC9:BC14	BC9	=LET( d, UNIQUE(FILTER($BC$25:$BC$31,($BA$25:$BA$31=BB9))), c,COUNTA(d), c )