Sumif using criteria and dates

[stugi]

Morning all,

I've found the posts regarding using date ranges in sumif formulas but I need to take it a stage futher.

I've got a summary sheet of depots and I want to report "total spend" so far but also identify spend for the "currnet" financial period.

the depot number is my criteria for "total" spend but how do I isolate the curent period spend?
Each spend transaction will ahve the depot number in col A, description in col B, the transaction date in col C and the value in col D.
I have a table which has the start and end date for each financial period and an input cell on the sumamry sheet to denote which period is the "current" one

cheers

 

[Akashwani]

Hi,

Sample data...

Excel Workbook
	A	B	C	D
1	ID Number	Name	Dates	Amount
2	AA10000	Bart Simpson	01/01/2011	1,000
3	AA10001	Lisa Simpson	02/01/2011	1,000
4	AA10002	Bart Simpson	03/01/2011	1,000
5	AA10000	Lisa Simpson	01/02/2011	2,000
6	AA10001	Marge Simpson	02/02/2011	2,000
7	AA10002	Maggie Simpson	01/03/2011	3,000
8	AA10002	Bart Simpson	01/04/2011	4,000
9	AA10001	Bart Simpson	02/04/2011	4,000
10	AA10002	Lisa Simpson	03/04/2011	4,000
11	AA10003	Homer Simpson	01/05/2011	5,000
12	AA10000	Bart Simpson	01/05/2011	5,000
13	AA10001	Bart Simpson	01/06/2011	6,000
14	AA10002	Lisa Simpson	01/06/2011	6,000
15	AA10000	Bart Simpson	04/07/2011	7,000
16	AA10001	Lisa Simpson	05/07/2011	7,000
17	AA10000	Homer Simpson	06/08/2011	8,000
18	AA10001	Maggie Simpson	02/08/2011	8,000
19	AA10002	Homer Simpson	09/09/2011	9,000
20	AA10001	Lisa Simpson	01/09/2011	9,000
Sheet1

This is summing up the dates based upon the period entered into A2....

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	Period	Start Date	End Date	Total	Total	*	Period	Start Date	End Date
2	1	01/01/2011	31/03/2011	10,000	10,000	*	1	01/01/2011	31/03/2011
3	*	*	*	*	*	*	2	01/04/2011	30/06/2011
4	*	*	*	*	*	*	3	01/07/2011	30/09/2011
5	*	*	*	*	*	*	4	01/10/2011	31/12/2011
Sheet2

This is summing up the same date range based upon A2 as well as the name entered into D2...

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	Period	Start Date	End Date	Name	Total	Total	*	Period	Start Date	End Date
2	1	01/01/2011	31/03/2011	Bart Simpson	2,000	2,000	*	1	01/01/2011	31/03/2011
3	*	*	*	*	*	*	*	2	01/04/2011	30/06/2011
4	*	*	*	*	*	*	*	3	01/07/2011	30/09/2011
5	*	*	*	*	*	*	*	4	01/10/2011	31/12/2011
Sheet2

The Sumproduct formula is for ALL versions of Excel.
The Sumifs formula is for 2007-2010 ONLY.

I hope that helps.

Ak

 

[Steve=True]

Sounds like a sumproduct to me.

Column A=DepotNum
Column C=TransDate
Column D=Value

Depot Choice is what depot you want to look up. You may want to make a table with the choices and place this formula next to each one of those choices.

=SUMPRODUCT((TransValue)*((TransDate)>=startfinperiod)*((TransDate)<=endfinperiod)*((DepotNum)=DepotChoice))

Hope this makes sense.

 

[stugi]

sumproduct it is then.

got it working well, thanks to both of you for your help