Sum of fields with multiple conditions

Katlj — Mon, 12 May 2014 17:48:31 GMT

I have a form which is going to be a financial statement summary of items from other lists. I have a sum field that looks at another list, and if the items are in the BPD division, the request if for equipment, and the fiscal year is 14, it sums up the actual amount. Works great. The current formula below works the way it's supposed to.

sum(xdXDocument:GetDOM("JBLM NEC--Exhibit 6")/dfs:myFields/dfs:dataFields/d:SharePointListItem_RW[d:DIV = "BPD"][d:Type_x0020_Of_x0020_Request = "Equipment"][d:FY = "14"]/d:Actual_x0020_Amount)

The problem is, I dont want to go in every year to change the [d:FY = "14"] to the new FY. I created another field on the form called Ex6SummaryFY1 for users to enter the FY they want to look at. I want the calculation to sum the actual amounts fields if the Divisons is BPD, the type is equipment, and the FY is the year the user enters into the Ex6SummaryFY1 field.

Ive tried:

sum(xdXDocument:GetDOM("JBLM NEC--Exhibit 6")/dfs:myFields/dfs:dataFields/d:SharePointListItem_RW[d:DIV = "BPD"][d:Type_x0020_Of_x0020_Request = "Equipment"][d:FY = ../my:EX6SummaryFY1]/d:Actual_x0020_Amount)

and a few other variations and I cant seem to get it there. Any ideas?

General

Sum of fields with multiple conditions