Wednesday, January 25, 2012

Sumproduct in InfoPath 2007

An workaround to do similar to excel Sumproduct function in InfoPath 2007

We had a requirement to calculate the sum of product of 2 column in a repeating table. See the screen shot below

Sumproduct in InfoPath 2007

Sales Tax is a Decimal field with Percentage format enabled
Quantity is an integer
Purchase Price is Decimal field
Monthly Total is Decimal field

We want the Monthly Total value to be
Monthly Total = Sum(Quality * Purchase Price) * percentage of Sales Tax

Since there is no option to do the sum(Quality * Purchase) in InfoPath as  SUM XPath function takes only a node set as parameter, I've created a field named QuantityPurchasePrice to have the product of Quality and Purchase Price and made the field hidden by setting the conditional formatting as below

Sumproduct in InfoPath 2007


Now apply the following formula in the Total field

sum(QuantityPurchasePrice) * (1 + SalesTax)


..and with some sample value, the output is

Calculate Sumproduct in InfoPath 2007



2 comments:

  1. Hello! I tried this and can't get it to work. I added the hidden multiplication field (and actually have it showing to make sure the calculation runs correctly), and I have the sum of that hidden field, and nothing shows up. I've read on other posts that it isn't possible to perform calculations on calculated fields. Any ideas?

    ReplyDelete
    Replies
    1. The calculations are done in the normal text fields. Are you able to resolve the formula in the field?

      Delete