Using Sumproduct in Multiple Columns

Anbuselvam K

Member
Dear Excel Genius,

I use the Sumproduct function in the alternate cells to calculate the RM Purchase cost. 3 sample formulas and their snapshot are below.

=SUMPRODUCT(Purchase[Price RM1],Purchase[Qty RM1])

=SUMPRODUCT(Purchase[Price RM2],Purchase[Qty RM2])

=SUMPRODUCT(Purchase[Price RM3],Purchase[Qty RM3])

I need one formulation to calculate the multiple RM Cost with one sumproduct or any other formula?

Example expected formula is =SUMPRODUCT(Purchase[Price RM1:RM34],Purchase[Qty RM1:RM34])

The actual sheet has RM1 to RM100, So One by one if I add the sumproduct then it will be a long process. Also, Similar to another case I have 1 to 1000 to be calculated.

So if someone provides a better formula for all the RM Cost in one cell with one formula will be greater help for me.

Attached is the sheet which is having 34 RM as an example sheet.

Attachments

• 30.3 KB Views: 2

p45cal

Well-Known Member
In your attached file in cell C5:
Code:
``=IF(LEFT(Purchase[[#Headers],[Price RM1]],3)="Pri",SUMPRODUCT(Purchase[Price RM1],Purchase[Qty RM1]),SUBTOTAL(109,C7:C21))``
copy across.