Multiple within the same column with criteria

[wsw979]

Hi,

I am wondering if there is anyway to calculate the Product for column "value" based on category with just using excel formula?

For example, based on the table below. I want to find the product for category A = 1*2, category B = 3*2*2 and C = 3*1*2.

I tried using SUMPRODUCT but instead of multiply, it sum the array.

Category	value
A	1
A	2
B	3
B	2
B	2
C	3
C	1
C	2

<colgroup><col width="80" span="2" style="width:60pt"> </colgroup><tbody>
</tbody>

 

[lrobbo314]

Easy to do using Power Query.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Products = Table.Group(Source, {"Category"}, {{"Product", each List.Product([value]), type number}})
in
    Products

 

[lrobbo314]

Or a formula version. In cells F2:F4, I have A,B, and C.

=PRODUCT(IF($A$2:$A$9=F2,$B$2:$B$9))

This is an array formula so, Ctrl+Shift+Enter.