SUMIF DATE RANGE??

MidnightMarauders

New Member
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
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

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
So if the date in A is within a given range you want to sum the number from C?

azcat90

New Member
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
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.

MrExcel MVP

Book1
ABCD
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
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

New Member

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

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

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

MidnightMarauders

New Member
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

MrExcel MVP
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...
...

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.

Replies
2
Views
59
Replies
22
Views
141
Replies
5
Views
55
Replies
3
Views
246
Replies
7
Views
65