SUMIF DATE RANGE??

[MidnightMarauders]

Hi All,

I am looking for a way to add the contents of a cell if another cell in its row is within a date range. I am not looking to count the rows so I believe SUMPRODUCT will not work for this?

In the example below I want to add the totals in Column C for each NAME in Column B depending on the date range..

Any ideas on accomplishing this would be greatly appreciated!!

EXAMPLE.xls
	A	B	C	D
1
2	COMPLETED	NAME	NUMBER
3	9/17/2006	Bob	31
4	9/31/2006	Bob	10
5	9/31/2006	Ben	5
6	10/5/2006	Bob	6
7	10/9/2006	Ben	7
8
9
10
11		9/16-9/31	10/1-10/15
12	Bob	??	??
13	Ben	??	??
14
15
Sheet1

Thanks Very Much,
Isaac

 

[OX Driver]

So if the date in A is within a given range you want to sum the number from C?

 

[azcat90]

Pull in the week for each date

Add a hidden tab cotaining a range of cells that lists the week associated with each date.

Add a column that will do a vlookup to pull in the week.

Add another column that will concatenate the week with the name.

Below in the summary use sumif to sum the numbers for each "NameWeek".

 

[big_mac]

Change your 9/31/2006 dates to numbers by correctly entering them as 9/30/2006.
Place your low range date into cell B10,
then your highrange date in cell B11,
then copy this formula into cell B12 and confirm with Ctrl+shift+enter
=SUM(IF($A$3:$A$8>=B$10,IF($A$3:$A$8<=B$11,IF($B$3:$B$8=$A12,$C$3:$C$8))))

Sorry I haven't installed the HTML maker to this computer yet but the formula works.

 

[Aladin Akyurek]

Book1
	A	B	C	D
2	COMPLETED	NAME	NUMBER
3	9/17/2006	Bob	31
4	9/30/2006	Bob	10
5	9/30/2006	Ben	5
6	10/5/2006	Bob	6
7	10/9/2006	Ben	7
8
9
10		9/16/2006	10/1/2006
11		9/30/2006	10/15/2006
12	Bob	41	6
13	Ben	5	7
Sheet1

B12, copied across and down:

=SUMPRODUCT(--($B$3:$B$7=$A12),--($A$3:$A$7>=B$10),--($A$3:$A$7<=B$11),$C$3:$C$7)

 

[MidnightMarauders]

B12, copied across and down:

=SUMPRODUCT(--($B$3:$B$7=$A12),--($A$3:$A$7>=B$10),--($A$3:$A$7<=B$11),$C$3:$C$7)

Excellent. Thanks sooo much, thats works perfectly!!

 

[MidnightMarauders]

Ok so what if sometimes I have multiple people on the same row, how can I adjust for additional columns??

Thanks in advance.

EXAMPLE.xls
	A	B	C	D	E	F	G	H	I
1
2	COMPLETED	NAME1	NUMBER	NAME2	NUMBER	NAME3	NUMBER	NAME4	NUMBER
3	9/17/2006	Bob	31	Ben	6
4	9/30/2006	Susan	10	Ben	10	Bob	25
5	9/30/2006	Joey	5	Jack	5	Bob	5	Ben	20
6	10/5/2006	Bob	6
7	10/9/2006	Ben	7
8
9
10		9/16/2006	10/1/2006
11		9/30/2006	10/15/2006
12	Bob	31	6
13	Ben	0	7
14	Susan	10	0
15	Joey	5	0
16	Jack	0	0
17	Craig	0	0
18	Jennifer	0	0
Sheet1

 

[Aladin Akyurek]

You'd be better off if you use a single list of 3 columns... That is, put everything in A:C.

 

[MidnightMarauders]

You'd be better off if you use a single list of 3 columns... That is, put everything in A:C.

I know, I dont get a choice of how it is formatted...

Basically I have a batch that up to 6 people can work on...

I actually just figured out how to adjust the formula to account for the 6 10 additional columns, but I now I need one that filters by type and I get a formula is toooooooo long!!

=SUMPRODUCT(--($U$2:$U$1000=$B4),--($P$2:$P$1000>=E$2),--($P$2:$P$1000<=E$3),$V$2:$V$1000)+SUMPRODUCT(--($W$2:$W$1000=$B4),--($P$2:$P$1000>=E$2),--($P$2:$P$1000<=E$3),$X$2:$X$1000)+SUMPRODUCT(--($Y$2:$Y$1000=$B4),--($P$2:$P$1000>=E$2),--($P$2:$P$1000<=E$3),$Z$2:$Z$1000)+SUMPRODUCT(--($AA$2:$AA$1000=$B4),--($P$2:$P$1000>=E$2),--($P$2:$P$1000<=E$3),$AB$2:$AB$1000)+SUMPRODUCT(--($AC$2:$AC$1000=$B4),--($P$2:$P$1000>=E$2),--($P$2:$P$1000<=E$3),$AD$2:$AD$1000)+SUMPRODUCT(--($AE$2:$AE$1000=$B4),--($P$2:$P$1000>=E$2),--($P$2:$P$1000<=E$3),$AF$2:$AF$1000)

Then I need to add the --($G$2:$G$1000="A or B")

All of the data is on a seperate sheet so after all the sheet identifiers it wont let me add the TYPE filter...

Is there a way to shorten the formula?

Thanks,
Isaac

 

[Aladin Akyurek]

You'd be better off if you use a single list of 3 columns... That is, put everything in A:C.

I know, I dont get a choice of how it is formatted...
...

Referring to your last exhibit...

B12:

=SUM(IF($A$3:$A$7>=B$10,IF($A$3:$A$7<=B$11,IF($B$3:$H$7=$A12,$C$3:$I$7))))

which is confirmed with control+shift+enter (not with enter) then copied across and down.