SumIIf with multiple paramaters

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Can you do a SumIIf with more than one paramete?. I want to do a sum where
the year is 2006 AND the months are July through December.
 
Wild guess follows since you gave no details on what you want to sum,
whether you are using a date field and extracting the year and month from
the date field or if you have a Year field and a Month field. Also, where
do you want to do this - in the report's record source (a query) or in the
report layout?

SUM (IIF (Year([SomeDate]) = 2006 AND Month([SomeDate]) > 6,[The Field to
Sum],Null))

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
I just have a Month and Year field. No Date. Doing this in the report
layout. I have a text box for each month that gives me the monthly totals by
using the formula below

=Sum(IIf([Year]='2006' And [Month]='January',[SumofInvQty],Null))


What I actually need now is to create a YTD total for the months of the
previous year for only months of the current year that have shipment totals.
For example, from the table below there is only data the first two months in
2007 so I need the 2006 YTD total to sum on those just those two months.
Then when March 2007 Data appears the 2006 YTD will include Jan, Feb & March
and so on.


2007 2006
January 5 4
February 5 4
March 7
April 8
May 9
June 6
July 5
August 4
September 5
October 6
November 5
December 3

YTD Total 10 8

Grand Total 10 66

John Spencer said:
Wild guess follows since you gave no details on what you want to sum,
whether you are using a date field and extracting the year and month from
the date field or if you have a Year field and a Month field. Also, where
do you want to do this - in the report's record source (a query) or in the
report layout?

SUM (IIF (Year([SomeDate]) = 2006 AND Month([SomeDate]) > 6,[The Field to
Sum],Null))

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Supe said:
Can you do a SumIIf with more than one paramete?. I want to do a sum
where
the year is 2006 AND the months are July through December.
Click to expand...
Click to expand...
 
Try this ---
SUM (IIF([SomeDate] Between #7/1/2006# AND #12/31/2006#, [The Field to
Sum],Null))

--
KARL DEWEY
Build a little - Test a little


John Spencer said:
Wild guess follows since you gave no details on what you want to sum,
whether you are using a date field and extracting the year and month from
the date field or if you have a Year field and a Month field. Also, where
do you want to do this - in the report's record source (a query) or in the
report layout?

SUM (IIF (Year([SomeDate]) = 2006 AND Month([SomeDate]) > 6,[The Field to
Sum],Null))

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Supe said:
Can you do a SumIIf with more than one paramete?. I want to do a sum
where
the year is 2006 AND the months are July through December.
Click to expand...
Click to expand...
 
Back
Top