Skip to main content

Notifications

Power Apps - Building Power Apps
Answered

Sumproduct in Power APPs

(1) ShareShare
ReportReport
Posted on by 36

Hi there, 

 

I would like to build a calculation in Power Apps to duplicate the feature of SUMPRODUCT function in Excel: 

A11 is an input of a fixed rate, A14-A20 are tiers inputs, B14-B20 are +/- rates inputs. C14-C20 are based on the Fixed Rate +/- the rate from column B, e.g.: B14 = 1%, so C14 = fixed rate 5% + B14 = 6%.  

And we will have another input B23 which is an amount, I d like to apply sumproduct function, here is how to calculate: 

(sumup in Column E) 

(if b23 > A14, (b23-a14)* C14 )+ (if b23>A15, (b23 -A15)*C15) ... until (if b23>A20, (b23-A20) *C20)  

MiaSunny_1-1719946388566.png

 

MiaSunny_2-1719946838084.png

Now i build the matrix A14:C20 into a gallery, i could referral the numbers by using: 

Lookup(gallery, TierID = varRowIndex, Tiers) to get the tiers or the same format to get the rates. 

All other cells in input boxes or labels, also easy to referral. And i am looking for a better way than: 

 

If( lbl_Amount > Lookup(gallery, TierID = 1, Tiers),

    (lbl_Amount - Lookup(gallery, TierID = 1, Tiers)) * Lookup(gallery, TierID = 1, Rates),

    0) +

If( lbl_Amount > Lookup(gallery, TierID = 2, Tiers),

    (lbl_Amount - Lookup(gallery, TierID = 2, Tiers)) * Lookup(gallery, TierID = 2, Rates),

    0) +

...

If( lbl_Amount > Lookup(gallery, TierID = 7, Tiers),

    (lbl_Amount - Lookup(gallery, TierID = 7, Tiers)) * Lookup(gallery, TierID = 7, Rates),

    0) 

I could get the result correctly and am expecting a way with better efficiency and reusable. 

 

Thank you! 

  • Verified answer
    Giraldoj Profile Picture
    Giraldoj 504 on at
    Re: Sumproduct in Power APPs

    Hey there @MiaSunny ,

    I see you want to replicate the SUMPRODUCT function from Excel in Power Apps. Here's a simpler and more efficient way to do it without all those If statements.

    Step 1: Adjust Rates

    First, let's create a collection that adjusts the rates based on your fixed rate. You can do this in the OnVisible property of your screen or somewhere similar:

     

    ClearCollect(
    
     AdjustedRates,
    
     AddColumns(
    
     GalleryMatrix.AllItems,
    
     "AdjustedRate", txtFixedRate + Rates
    
     )
    
    );

     

    This will give us a new column called AdjustedRate for each item in your gallery.

    Step 2: Calculate SUMPRODUCT

    Next, we'll use the ForAll function to go through the AdjustedRates collection and perform the calculations. Here's how you can do it in a label to display the result:

     

    Sum(
    
     ForAll(
    
     AdjustedRates,
    
     If(
    
     txtAmount > Tiers,
    
     (txtAmount - Tiers) * AdjustedRate,
    
     0
    
     )
    
     ),
    
     Value
    
    )

     

     

    In the first step, we create a collection called AdjustedRates that includes an extra column for the adjusted rates, which is your fixed rate plus each rate from the gallery.

    In the second step, we use ForAll to iterate through the AdjustedRates collection and perform the SUMPRODUCT calculation. This sums up all the products where the amount is greater than the tier, multiplied by the adjusted rate.

    So, instead of writing tons of If statements, we’re using ForAll to loop through your gallery items and do the math. This should make your app more efficient and easier to manage.

    If this helps, please mark it as the solution by clicking "Accept as solution." A "Thumbs Up" would be greatly appreciated if you found the content helpful. Thanks!

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Microsoft Kickstarter Events…

Register for Microsoft Kickstarter Events…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Announcing Forum Attachment Improvements!

We're excited to announce that attachments for replies in forums and improved…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 145,422

#2
RandyHayes Profile Picture

RandyHayes 76,287

#3
Pstork1 Profile Picture

Pstork1 64,711

Leaderboard