• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Using Sumproduct in Multiple Columns

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.



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