Summinh with If Function

[WorkRelated]

I have a report that shows the start date and finish date of a job and the
contract value.

I want to create a sum in the report footer that adds the contract value if
the start date and end date fall within the same month, as I am fairly new to
access I am not sure how to set this up. Please help. Thanks

 

[Pecoflyer]

If start month in col A, end in col B,contract in col C

try

=SUM(IF(MONTH(A2:A9)=MONTH(B2:B9),C2:C9,0))

Adapt the ranges to your needs

Be sure to enter as an array formula, that is put the insertion poin
in the formula bar and press Ctrl+Shift+enter at the same time
Each time you edit the formula you will have to do this agai

--
Pecoflye

Cheers -

Firefox 3.5.3 is as slow as possible

 

[Klatuu]

Pecoflyer's response is an Excel answer.

There is no SumIf in Access, but you could use an IIf to do that:

=Sum(IIf(Month([StartDate) = Month([EndDate]), [ContractValue], 0)

 

[John Spencer]

=Sum(IIF(Format(StartDate,"yymm")=Format(EndDate,"yymm"),ContractValue,0))

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County