Formula Request

[Muthukrishnan V]

Formula Request.xlsx
	A	B	C	D	E	F	G	H	I
1
2					Helper		Required Output
3	Name	Amount		Region	Kyn		Region	Name	Amount
4	Aaa	25,000		Xyz			Kyn	Ggg	17,000
5	Bbb	15,000		Kyk				Mlm	3,000
6	Ccc	60,000		Qrs				Ggg	25,000
7	Ddd	40,000		Qrs				3	45000
8	Eee	8,500		Tuv
9	Ggg	17,000		Kyn
10	Lll	20,000		Tuv
11	Kkk	9,500		Pqr
12	Aaa	90,000		Xyz
13	Aaa	30,000		Xyz
14	Aaa	75,000		Xyz
15	Mlm	3,000		Kyn
16	Kkk	1,00,000		Pqr
17	Lll	80,000		Tuv
18	Kkk	60,000		Pqr
19	Lll	50,000		Tuv
20	Ggg	25,000		Kyn
Sheet1

Cell Formulas
Range	Formula
E3	E3	=D9

I request you to kindly advise me a formula to get the required output in Columns G H and I (If possible the sum of amount and number of counts). Thanks

 

[Cubist]

Try:

Book1
	A	B	C	D	E	F	G	H	I
1
2					Helper		Required Output
3	Name	Amount		Region	Kyn		Region	Name	Amount
4	Aaa	25000		Xyz			Kyn	Ggg	17000
5	Bbb	15000		Kyk				Mlm	3000
6	Ccc	60000		Qrs				Ggg	25000
7	Ddd	40000		Qrs				3	28000
8	Eee	8500		Tuv
9	Ggg	17000		Kyn
10	Lll	20000		Tuv
11	Kkk	9500		Pqr
12	Aaa	90000		Xyz
13	Aaa	30000		Xyz
14	Aaa	75000		Xyz
15	Mlm	3000		Kyn
16	Kkk	100000		Pqr
17	Lll	80000		Tuv
18	Kkk	60000		Pqr
19	Lll	50000		Tuv
20	Ggg	25000		Kyn
Sheet4

Cell Formulas
Range	Formula
E3	E3	=D9
H4:I7	H4	=LET(f,FILTER(A4:B20,D4:D20=G4),ct,ROWS(f),s,SUM(DROP(f,1)),IFERROR(VSTACK(f,HSTACK(ct,s)),""))
Dynamic array formulas.

 

[Muthukrishnan V]

Mr Cubist sir, the total amount should show 45000 and not 28000. Request amendment to formula. Thanks

Also Region to be picked up from Helper cell E4. Thanks

 

[Cubist]

Oops missed an argument there. Try:

=LET(f,FILTER(A4:B20,D4:D20=E4),ct,ROWS(f),s,SUM(f),IFERROR(VSTACK(f,HSTACK(ct,s)),""))

 

[Muthukrishnan V]

Kindly help me so that the formula picks up the Region as in E4. Thank you sir

 

[Cubist]

Ok,

=LET(f,FILTER(A4:B20,D4:D20=E3),ct,ROWS(f),s,SUM(f),IFERROR(HSTACK(E3,VSTACK(f,HSTACK(ct,s))),""))

 

[Muthukrishnan V]

Works fine, Mr Cubist sir, Thanks,
I