Multi Count Formula Needed

[tmk0427]

Hi, AI can't help me so I turned to humans.

In the table below is as follows:

Column A is list of random names (last name, first name),
Column D is a list of specific codes
Columns F-AJ (columns I-Aj are hidden for sake of brevity) are days in a month 1-31 (in this case only days 1-3 show)
Column AK total of values of rows F-AJ (in this case only values total for columns F-H)
Column AL2:AL9 is where I want only unique names from column A. In this example, AL2 should result in Buck, Jim and AL3 should result in Smith, John.

	A	B	C	D	E	F	G	H	AK	AL	AM	AN
	Name	MRN	ICD	CPT	Desc	1	2	3	Total	Unique Name	Days	RT94668
2	Buck, Jim			RT94668		8	8	8	24
3	Buck, Jim			RT94761		8	8	8	24
4	Buck, Jim			RT94010		4	4		8
5	Buck, Jim			RT94060		4	4		8
6	Smith, John			RT94668		8	8		16
7	Smith, John			RT94761		8	8		16
8	Smith, John			RT94010		4			4
9	Smith, John			RT94060		4			4

Then in AM2 and down under the Days column, I need a formula that counts the cells in each row with the most values for F1:H9 but only for the unique name in AL2 and AL3. In this example, AM2 should result in 3 days for AL2 Buck, Jim and AM3 should result in 2 days for AL3 Smith, John.
The formula in column AM should look at F1:H9 and only count the most values in each row associated with Buck, Jim (AL2) and Smith, John (AL3). In this case, AM2 should see that F2-H2 and F3-H3 have 3 values total in each row and F4-H4 and F5-H5 only have 2. For AM3, rows F6-H6 and F7-H7 have 2 values total and rows F8-H8 and F9-H9 only have 1 value total.

Then in column AN under the RT94668 column I need a formula that sums the values F1:H9 that match column D "RT94668" for the Unique name in column AL. In this case, AN2 the formula should sum the row with CPT code RT94668 in column D under Buck, Jim which would be 24. In AN3, the formula would sum the row with CPT code RT94668 in column D under Smith, John which would be 16.

I hope this makes sense and I am open to other ways of configuring this table to make it easier to get my desired results.

Thank you in advance, humans.

Terence

 

[hagia_sofia]

Hello, humans strike back, i.e. try the following in AL2:

=LET(
n,A2:A9,
cpt,D2:D9,
t,F2:H9,
code,AN1,
u,UNIQUE(n),
d,MAP(u,LAMBDA(x,MAX(FILTER(BYROW(t,COUNTA),n=x)))),
c,MAP(u,LAMBDA(y,SUM(FILTER(t,(n=y)*(cpt=code))))),
HSTACK(u,d,c))

 

[tmk0427]

Hello, humans strike back, i.e. try the following in AL2:

=LET(
n,A2:A9,
cpt,D2:D9,
t,F2:H9,
code,AN1,
u,UNIQUE(n),
d,MAP(u,LAMBDA(x,MAX(FILTER(BYROW(t,COUNTA),n=x)))),
c,MAP(u,LAMBDA(y,SUM(FILTER(t,(n=y)*(cpt=code))))),
HSTACK(u,d,c))

Wow. Humans are still necessary . That's an amazing formula. What would I need to do to tailor that formula to count the other cpt codes such as RT94761 or RT94010 etc? Thank you.

 

[tmk0427]

Wow. Humans are still necessary . That's an amazing formula. What would I need to do to tailor that formula to count the other cpt codes such as RT94761 or RT94010 etc? Thank you.

In other words, if I wanted to add RT94761 to AO1 and so forth along the row.

 

[hagia_sofia]

Many thanks for the feedback, try the following:

=LET(
n,A2:A9,
cpt,D2:D9,
t,F2:H9,
code,AN1:AO1,
u,UNIQUE(n),
d,MAP(u,LAMBDA(x,MAX(FILTER(BYROW(t,COUNTA),n=x)))),
c,DROP(REDUCE("",code,LAMBDA(a,b,HSTACK(a,MAP(u,LAMBDA(y,SUM(FILTER(t,(n=y)*(cpt=b)))))))),,1),
HSTACK(u,d,c))

 

[tmk0427]

Many thanks for the feedback, try the following:

=LET(
n,A2:A9,
cpt,D2:D9,
t,F2:H9,
code,AN1:AO1,
u,UNIQUE(n),
d,MAP(u,LAMBDA(x,MAX(FILTER(BYROW(t,COUNTA),n=x)))),
c,DROP(REDUCE("",code,LAMBDA(a,b,HSTACK(a,MAP(u,LAMBDA(y,SUM(FILTER(t,(n=y)*(cpt=b)))))))),,1),
HSTACK(u,d,c))

Thank you.

 

[tmk0427]

Thank you.

One more question. how do I get rid of the #CALC! error message when there is no values matching the code? Thanks.

 

[tmk0427]

One more question. how do I get rid of the #CALC! error message when there is no values matching the code? Thanks.

and make it show zero instead

 

[hagia_sofia]

Ah, apologies, my omission; just the last part needs to adjusted, i.e.

IFERROR(HSTACK(u,d,c),0))