# 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

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

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.

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.

### Which adblocker are you using?

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

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