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
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

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,210

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,210
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,210
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,026
Messages
5,856,930
Members
431,839
Latest member
Guest user

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top