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.

[TABLE="width: 160"]
<colgroup><col width="80" span="2" style="width:60pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 80, align: center"]Category[/TD]
[TD="class: xl65, width: 80, align: center"]value[/TD]
[/TR]
[TR]
[TD="class: xl65, align: center"]A[/TD]
[TD="class: xl65, align: center"]1[/TD]
[/TR]
[TR]
[TD="class: xl65, align: center"]A[/TD]
[TD="class: xl65, align: center"]2[/TD]
[/TR]
[TR]
[TD="class: xl65, align: center"]B[/TD]
[TD="class: xl65, align: center"]3[/TD]
[/TR]
[TR]
[TD="class: xl65, align: center"]B[/TD]
[TD="class: xl65, align: center"]2[/TD]
[/TR]
[TR]
[TD="class: xl65, align: center"]B[/TD]
[TD="class: xl65, align: center"]2[/TD]
[/TR]
[TR]
[TD="class: xl65, align: center"]C[/TD]
[TD="class: xl65, align: center"]3[/TD]
[/TR]
[TR]
[TD="class: xl65, align: center"]C[/TD]
[TD="class: xl65, align: center"]1[/TD]
[/TR]
[TR]
[TD="class: xl65, align: center"]C[/TD]
[TD="class: xl65, align: center"]2[/TD]
[/TR]
</tbody>[/TABLE]

 

[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.