General

Re: How to create a Sumproduct or Sumif formula in Infopath 2010

cbrown00 — Tue, 10 Feb 2015 06:17:19 GMT

Howdy Jimmy. I'm trying to figure out how to do a Sumproduct xpath formula. Hopefully my screenshot shows up below that shows the schema. The two items to be multiplied are outlined in red; they are in the same repeating group. I can get one sumproduct, but not the subsequent ones from new instances of the repeating group. Is it possible to make this work?

I searched and found your post on how to post an image. It says to use the Controls tab, but I don't see a Controls tab. I tried Insert/Edit image, but it isn't recognizing my PNG image.

Re: How to create a Sumproduct or Sumif formula in Infopath 2007

mdasifkhaleel — Wed, 23 Nov 2011 17:14:41 GMT

Thanks alot, you are star.. It is working now. I have replace "@SP-FOL1" with "../@SP-FOL1"

Re: How to create a Sumproduct or Sumif formula in Infopath 2007

Jimmy — Wed, 23 Nov 2011 03:27:53 GMT

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

Re: How to create a Sumproduct or Sumif formula in Infopath 2007

mdasifkhaleel — Tue, 22 Nov 2011 17:45:22 GMT

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.

Re: How to create a Sumproduct or Sumif formula in Infopath 2010

edwinbrito — Thu, 07 Jul 2011 02:16:39 GMT

It worked!!!

Thanks Jim. You are awesome!

Re: How to create a Sumproduct or Sumif formula in Infopath 2010

Jim Cantwell — Thu, 07 Jul 2011 01:51:28 GMT

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.

Re: How to create a Sumproduct or Sumif formula in Infopath 2010

edwinbrito — Thu, 07 Jul 2011 00:30:36 GMT

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

Re: How to create a Sumproduct or Sumif formula in Infopath 2010

edwinbrito — Wed, 06 Jul 2011 23:37:59 GMT

Re: How to create a Sumproduct or Sumif formula in Infopath 2010

edwinbrito — Wed, 06 Jul 2011 23:37:07 GMT

Great! let me try it...

Re: How to create a Sumproduct or Sumif formula in Infopath 2010

Jim Cantwell — Wed, 06 Jul 2011 15:28:01 GMT

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

Re: How to create a Sumproduct or Sumif formula in Infopath 2010

Jimmy — Wed, 06 Jul 2011 14:54:29 GMT

Could you attach a screenshot of your data source?

Re: How to create a Sumproduct or Sumif formula in Infopath 2010

edwinbrito — Wed, 06 Jul 2011 14:24:42 GMT

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

Re: How to create a Sumproduct or Sumif formula in Infopath 2010

edwinbrito — Wed, 06 Jul 2011 14:23:45 GMT

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

Re: How to create a Sumproduct or Sumif formula in Infopath 2010

edwinbrito — Wed, 06 Jul 2011 13:56:28 GMT

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

Thanks!

Re: How to create a Sumproduct or Sumif formula in Infopath 2010

Jimmy — Wed, 06 Jul 2011 03:42:41 GMT

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