Multiple within the same column with criteria

wsw979

New Member
Joined
Jul 17, 2019
Messages
2
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.

Categoryvalue
A1
A2
B3
B2
B2
C3
C1
C2

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

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
2,647
Office Version
365, 2019, 2016
Platform
Windows
Easy to do using Power Query.

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

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
2,647
Office Version
365, 2019, 2016
Platform
Windows
Or a formula version. In cells F2:F4, I have A,B, and C.

Code:
=PRODUCT(IF($A$2:$A$9=F2,$B$2:$B$9))
This is an array formula so, Ctrl+Shift+Enter.
 

Forum statistics

Threads
1,086,096
Messages
5,387,806
Members
402,077
Latest member
excelinedinburgh

Some videos you may like

This Week's Hot Topics

Top