Power Platform Community Forum Thread Details

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)

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),

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

Thank you!

Categories:

Building 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!