formula search, match, maeby sumproduct to get SUM if...?

[drom]

Hi and Thanks in advance!!

Check the attached Image

On the range D16:D24, I would like to have a formula (No UDF)

• To get the expected results see Yellow ($D$16:$D$24)

I have named the ranges I guess I am gonna need (see range("$H$17:$I$21")

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2				Project ID'S
3				P1	P2	P3	P4	P5	P6	P7	TOTAL
4
5				156	173	249	180	156	98	98	1.110
6
7		Proy Nº	Name	P1 PERSONAL	P2 ACT - MANT	P3 KIO	P4 GEST - ADM	P5 INV	P6 ZZZ	P7 No Imp	TOTAL
8		Pr01	AAAAA	49	7	33	10	61	10	52	222
9		Pr03	BBBBBB	10		65	20	89			184
10		Pr04	CCC	15	4	83	30		11		143
11		Pr05			95	2	40		23		160
12		Pr07	DDD	82	67	66	80	6	54	46	401
13		Pr15									0
14
15		Pssible Projects	Project ID	Amount
16		Pr03, Pr04	P3	148	=F9+F10
17		Pr03, Pr04	P1	25	=D9+D10			E3:K3	ProjectIDs
18		Pr03, Pr04	P4	50	=G9+G10			B8:B13	AA
19		Pr03, Pr04, Pr07	P2	71	=E9+E10+E12			C8:D13	BB
20		Pr04	P6	11	=I10
21		Pr01, Pr04	P4	40	=G8+G10			Table Name	Table1
22		Pr01, Pr03, Pr04, Pr07	P3	247	=F8+F9+F10+F12
23		Pr56	x		=""
24		Pr7	P45		=""
25
26
27
28
29
30
31
Sheet1

Cell Formulas
Range	Formula
D5	D5	=SUBTOTAL(9,Table1[P1 PERSONAL])
E5	E5	=SUBTOTAL(9,Table1[P2 ACT - MANT])
F5	F5	=SUBTOTAL(9,Table1[P3 KIO])
G5	G5	=SUBTOTAL(9,Table1[P4 GEST - ADM])
H5	H5	=SUBTOTAL(9,Table1[P5 INV])
I5	I5	=SUBTOTAL(9,Table1[P6 ZZZ])
J5	J5	=SUBTOTAL(9,Table1[P7 No Imp])
K5	K5	=SUBTOTAL(9,Table1[TOTAL])
K8:K13	K8	=SUM(Table1[@[P1 PERSONAL]:[P7 No Imp]])
D16	D16	=F9+F10
E16:E24	E16	=FORMULATEXT(D16)
D17	D17	=D9+D10
D18	D18	=G9+G10
D19	D19	=E9+E10+E12
D20	D20	=I10
D21	D21	=G8+G10
D22	D22	=F8+F9+F10+F12
D23:D24	D23	=""

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
K8:K11	Expression	=and(A8<>K8;XEZ8<>$C$4)	text	NO
K12	Expression	=and(A11<>K12;XEZ11<>$C$4)	text	NO

Cells with Data Validation
Cell	Allow	Criteria
D8:K13	Whole number	>=0

Thanks!

 

[kvsrinivasamurthy]

For 2021 version Spill out formula In D16

=LET(a,B8:B13,b,D3:J3,MAP(B16:B24,C16:C24,LAMBDA(x,y,SUMPRODUCT(D8:J13*((ISNUMBER(FIND(a,x)))*(b=y))))))

For other versions formula In D16, to be copied down till D24

=SUMPRODUCT($D$8:$J$13*((ISNUMBER(FIND($B$8:$B$13,$B16)))*($D$3:$J$3=$C16)))

 

[kvsrinivasamurthy]

More precise Formulas
For 2021 version Spill out formula In D16

=LET(a,B8:B13,b,D3:J3,MAP(B16:B24,C16:C24,LAMBDA(x,y,SUMPRODUCT(D8:J13*((ISNUMBER(FIND(", "&a&",",", "&x&",")))*(b=y))))))

For other versions formula In D16, to be copied down till D24

=SUMPRODUCT($D$8:$J$13*((ISNUMBER(FIND(", "&$B$8:$B$13&",",", "&$B16&",")))*($D$3:$J$3=$C16)))

 

[Fluff]

Xl 2021 does not have the Lambda functions.