SUMPRODUCT inside Dynamic Array with CHOOSE - impossible?

[LuisPulido]

Hi!

So I want to make a Dynamic array with Choose, where one column will be the unique clients and in the second column I want the SUMPRODUCT of Quantity and Price for each product.

As you can see the result is 0 for each row.

This is just an example of my real problem with a report of 200K rows. For this reason I can not add another column (auxiliar column where I multiply Quantity and Product) and use SUMIFS as in this example. Make a pivot table is not an option neither.

I´ve been working on this for two days so I think this is real challenge.

Thanks in advance for your help.

I am open to another formulations.

Book1
	B	C	D	E	F	G	H
3
4	Client	Quantity	Price
5	A	19	23			A	0
6	B	12	15			B	0
7	C	16	22			C	0
8	B	13	11			D	0
9	B	29	12
10	B	15	14
11	D	14	15
12	C	25	27
13	A	16	21
14	C	29	27
15	C	10	23
16	A	18	30
17	D	27	11
18	D	22	18
19	A	23	11
20	A	22	18
21	C	29	17
22	B	20	17
23	A	13	13
24	C	19	22
Sheet1

Cell Formulas
Range	Formula
G5:H8	G5	=CHOOSE({1,2}, UNIQUE(B5:B24), SUMPRODUCT( C5:C24, D5:D24, ISNUMBER(MATCH(B5:B24,@UNIQUE(B5:B24),0)) ) )
Dynamic array formulas.

 

[Fluff]

Your formula would need to be

=CHOOSE({1,2},UNIQUE(B5:B24),SUMPRODUCT(C5:C24,D5:D24,--(ISNUMBER(MATCH(B5:B24,UNIQUE(B5:B24),0)))))

but that will give you the total sum ie 7206 on each row.
You might be able to do it using offset or mmult, but I have no idea how. You would be better off just using a formula for the distinct values & then use the sumproduct separately.