"ProductIF" with Spill

[alibini]

Hi, I am trying to do something simple but finding it very hard.

I have a table like this:

Name	Ticket
Bill	7
Bill	5
Bill	8
Tom	6
Tom	7
Tom	4
James	2
James	3

I want to add one SPILL/Dynamic formula in a third column to give me the product of all Ticket values for each name. I don't mind if it is duplicated or not.

The result can look like one of these:

Name	Ticket	Product	Name	Ticket	Name	Product
Bill	7	280	Bill	7	Bill	280
Bill	5	280	Bill	5	Tom	168
Bill	8	280	Bill	8	James	6
Tom	6	168	Tom	6
Tom	7	168	Tom	7
Tom	4	168	Tom	4
James	2	6	James	2
James	3	6	James	3

I can only succeed with this task if I apply a formula in every row rather than dynamically.

Many thanks and HNY.

 

[hagia_sofia]

Hello, one way to do it could be:

=MAP(A2:A9,LAMBDA(x,PRODUCT(FILTER(B2:B9,A2:A9=x))))

 

[alibini]

Hello, one way to do it could be:

=MAP(A2:A9,LAMBDA(x,PRODUCT(FILTER(B2:B9,A2:A9=x))))

Brilliantly simple solution. Thanks so much.

 

[Peter_SSs]

IF you have the new GROUPBY function you could also do it simply like this

25 01 01.xlsm
	A	B	C	D	E
1	Name	Ticket
2	Bill	7		Bill	280
3	Bill	5		James	6
4	Bill	8		Tom	168
5	Tom	6
6	Tom	7
7	Tom	4
8	James	2
9	James	3
Products

Cell Formulas
Range	Formula
D2:E4	D2	=GROUPBY(A2:A9,B2:B9,PRODUCT,,0)
Dynamic array formulas.

Another option might be to use Excel's built-in Pivot Table feature (on the Insert ribbon tab)

25 01 01.xlsm
	A	B	C	D	E
1	Name	Ticket		Row Labels	Product of Ticket
2	Bill	7		Bill	280
3	Bill	5		James	6
4	Bill	8		Tom	168
5	Tom	6
6	Tom	7
7	Tom	4
8	James	2
9	James	3
Products (2)