SUMIF DATE RANGE??

MidnightMarauders

New Member
Joined
Feb 2, 2005
Messages
32
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
ABCD
1
2COMPLETEDNAMENUMBER
39/17/2006Bob31
49/31/2006Bob10
59/31/2006Ben5
610/5/2006Bob6
710/9/2006Ben7
8
9
10
119/16-9/3110/1-10/15
12Bob????
13Ben????
14
15
Sheet1


Thanks Very Much,
Isaac
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

OX Driver

New Member
Joined
May 7, 2003
Messages
41
So if the date in A is within a given range you want to sum the number from C?
 

azcat90

New Member
Joined
Dec 6, 2005
Messages
21
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

Board Regular
Joined
Jul 21, 2006
Messages
167
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201

ADVERTISEMENT

Book1
ABCD
2COMPLETEDNAMENUMBER
39/17/2006Bob31
49/30/2006Bob10
59/30/2006Ben5
610/5/2006Bob6
710/9/2006Ben7
8
9
109/16/200610/1/2006
119/30/200610/15/2006
12Bob416
13Ben57
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

New Member
Joined
Feb 2, 2005
Messages
32

ADVERTISEMENT

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
ABCDEFGHI
1
2COMPLETEDNAME1NUMBERNAME2NUMBERNAME3NUMBERNAME4NUMBER
39/17/2006Bob31Ben6
49/30/2006Susan10Ben10Bob25
59/30/2006Joey5Jack5Bob5Ben20
610/5/2006Bob6
710/9/2006Ben7
8
9
109/16/200610/1/2006
119/30/200610/15/2006
12Bob316
13Ben07
14Susan100
15Joey50
16Jack00
17Craig00
18Jennifer00
Sheet1
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
You'd be better off if you use a single list of 3 columns... That is, put everything in A:C.
 

MidnightMarauders

New Member
Joined
Feb 2, 2005
Messages
32
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!!

Code:
=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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,400
Messages
5,547,735
Members
410,809
Latest member
lilwayne
Top