InfoPath Dev

I am creating a form in SharePoint 2010 using InfoPath 2010. I need to sum a field in a repeating table when a condition is meet. In Excel this is easy to do with the Sumif function. How can I do that in InfoPath?

In the same form I need to do a Sumproduct. I don't know how to code. Have somebody found the equivalent of Sumif and Sumproduct in InfoPath 2010?

Please advise...

In InfoPath, the xpath provides the "if" logic for your sum operation.  Take the following schema for expenses, and you want to sum all approved expenses.

<my:myFields>

    <my:Expenses>

        <my:Expense my:approved='false' my:amount='23.55' my:desc='lunch' />

        <my:Expense my:approved='true' my:amount='50.63' my:desc='taxi' />

    </my:Expenses>

</my:myFields> 

You can add a calculated value to your form with the following to total approved expenses:

sum(/my:myFields/my:Expenses/my:Expense[@my:approved = "true"]/@my:amount) 

You can carry out a sumproduct (or sum any other aggregate expression) using the eval and sum functions:

sum(eval(/my:myFields/my:myGroup/my:myParentField, 'my:childField1 * my:childField2'))

I appreciate your response. Let me try to adapt it to my form.

Thanks!

Thanks Jimmy,

Still, I haven't been able to figure it out.

This is my situation: I need to calculate the total of color and B/W copies separately in a Printing Request Form.

This is the form:

I need to add the '# of pages' x 'Times' only for color and B/W printouts.

Thanks for your help!!

Thanks Jim,

Still, I haven't been able to figure it out.

This is my situation: I need to calculate the total of color and B/W copies separately in a Printing Request Form.

This is the form:

I need to add the '# of pages' x 'Times' only for color and B/W printouts.

Thanks for your help!!

Could you attach a screenshot of your data source?

Using Jimmy's example, just add the filter to the xpath.

sum(eval(/my:myFields/my:myGroup/my:myParentField[my:Color='B/W'], 'my:childField1 * my:childField2'))

Great! let me try it...

I don't know what I'm doing wrong. I am getting the following error:

Node-test expected.
sum(eval(../my:ByDocID/my:group2/-->[<--my:IDColor='B/W'], '../my:ByDocID/my:group2/my:IDNoPages * ../my:ByDocID/my:group2/my:IDTimes'))

this is the formula with the changes to fit it in to my fields:

sum(eval(../my:ByDocID/my:group2/[my:IDColor='B/W'], '../my:ByDocID/my:group2/my:IDNoPages * ../my:ByDocID/my:group2/my:IDTimes'))

 Thanks for your help!!

You have a typo in the xpath, the left square bracket '[' should not be preceded by a forward slash '/'.  And the second argument to the eval function should be relative to the node which is returned by the xpath.

Try:

sum(eval(../my:ByDocID/my:group2[my:IDColor='B/W'], 'my:IDNoPages * my:IDTimes'))

One other thing, you are using a relative path "../" in your eval, this will be relative to the node where the xpath is used.  If you are using a calculated value control, it will be relative to the xpath of the section it is contained.  Or you can use a full xpath and not a relative xpath.

It worked!!!

Thanks Jim. You are awesome!

Jim

Hope you can help me.

Formula:

sum(xdMath:Eval(xdXDocument:GetDOM("Full_Download")/dfs:myFields/dfs:dataFields/dfs:Full_Download/@SP-ProcessAreas[. = xdXDocument:get-DOM()/my:myFields/my:field2 and ../@_.Project_Status = xdXDocument:get-DOM()/my:myFields/my:field3 and ../@SP-SubProcess = current()/../my:field9], "@SP-FOL1"))

Field2 is Infopath field Process Area 

Field3 is Infopath field Project Status

Field9 is Infopath field Sub Process

@SP-ProcessAreas is Secondary Data sources

@_.Project_Status is Secondary Data Sources

@SP-SubProcess is Secoundary Data Sources

What I am trying do:

Excel Sumproduct Example :

Sumproduct((SP-ProcessArea=Field2)*(_.ProjectStatus=Field3)*(SP-SubProcess=Field9)*SP-FOL1)

When I validated above XPath Expersion it display no error in formula. However when I run the form it display an error as Nan.

Your help in this much apprciated.

Can you verify that all of the @SP-FOL1 fields have a numeric value?  If any of them are non-numeric, you will get NaN.

 EDIT:  Actually, there's a problem with the part you have in quotes.  Since the context node is @SP-ProcessAreas, I think you need to have ../@SP-FOL1