# =Sumif([Fr Labor SUM],[FR Status]="Approved")

• ### Question

• Is there a way to do a SUMIF statement as the control source of a field in the header of a form?  I could do this with a query in a subform, but I am trying to do this without adding a subform (one calculation).  The sum feature works, but the criteria throws me off.

I need to sum the ['FR Labor Sum] if the [FR Status] = "Approved"

Note:  The form is [FR-Listing]

Thank you,
Mark Matzke

• Edited by Friday, August 1, 2014 3:43 PM Spelling
Friday, August 1, 2014 3:42 PM

• There are various expressions you could use, e.g.

=Sum([FR Labor Sum]*IIf([FR Status] = "Approved",1,0))

=Sum(IIf([FR Status] = "Approved",[FR Labor Sum],Null))

Ken Sheridan, Stafford, England

• Marked as answer by Friday, August 1, 2014 4:32 PM
Friday, August 1, 2014 3:58 PM

### All replies

• There are various expressions you could use, e.g.

=Sum([FR Labor Sum]*IIf([FR Status] = "Approved",1,0))

=Sum(IIf([FR Status] = "Approved",[FR Labor Sum],Null))

Ken Sheridan, Stafford, England

• Marked as answer by Friday, August 1, 2014 4:32 PM
Friday, August 1, 2014 3:58 PM
• Good Morning Mark,

As an example, here are formulas I have as the control source of a textboxes on Forms & Reports;

=IIf([AccountingID] Is Null,Null,Nz(DLookUp("SumOfAGAMOUNT","FEEAG","AccountingID=" & [AccountingID]),0))

=IIf(IsNull([Non-ActionPEB]),Null,"Determined No Action Required " & [Non-ActionPeb])

=IIf(IsNull([ApprovalDate]) And IsNull([ConditionedApprovalDate]) And IsNull([DisapprovalDate]) And IsNull([RemovedDate]) And IsNull([Non-ActionPEB]),"Pending",Null)

So using these working examples you can do something like

=IIf([FR Status] = "Approved", Sum(['FR Labor Sum]))

However, I think either you are trying to add fields together that are on the Form in the which case you would simply do like;

=Nz([RequiredFeesTextBox],0)+Nz([ManualFee],0)-Nz([Payment],0)

But if you are referring to a sum of numbers associated with a single record on a sub data sheet then you will most likely have to have the subForm on your Form to get the data unless you use a Query for your Form and have a calculated Field in your Query as an alias.

Example

([BalanceTestFees.SumOfTestFees]+[BalanceTrafficFees].[SumOfTrafficFees]+[BalanceManualFee].[SumOfManualFee]-[BalancePaymentInformation].[SumOfPayment]) AS SumofSums

Hth

Chris Ward

Friday, August 1, 2014 4:11 PM
• I see Ken has supplied some examples that are more concise.

I would defer to Ken as he is more experienced than I.

Chris Ward

Friday, August 1, 2014 4:13 PM