# Sumifs with 2 dates and weeknumber

#### ebbrey

##### Board Regular
Hi!
I have a callendar that shows how many workers are avaiable. Splitted by the week number in colums
And by who in rows. Above the weeknumber i need to show how many we need that week.

Been stuck with trying to convert the dates into week numbers and split what i need on the different weeks and match this and match this back. But im sure this can be written in one code.

I want the workforce needed total to show in Cell D20 for week 1 of a year. The date/week/year of this date is stored in D68-71 for other purposes.
The start date for a task is in Column I, end dates in Column J. And the workforce needed is automaticly calculated in column N (on a different sheet). The dates for start and end can be overlapping, one thing can start in 5 months other things in just a week. I need it to be able to work unsorted.

The number of work force needed in column N is in numbers by people needed per week.Ex.

 Start End People needed per week to do this task 01.03.2014 01.06.2014 3 01.01.2014 01.05.2014 6

<TBODY>
</TBODY>

(in this example i need 9 people from 01.03 until 1.05)

 In need of: ? ? ? ? ? Week number 1 2 3 4 5 Avaiable person Avaiable person

<TBODY>
</TBODY>

How shall i write this?

### Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
If you add two more columns to your Start and End Table you can do this:

Excel 2010
ABCDEFGHIJK
7StartEndWeek StartWeekEndPeople needed perweek to do this task
801/03/201401/06/20149223
901/01/201401/05/20141186
10
11
12
13In need of:????
14Week number12345678910
15People Required6666666699
Sheet1
Cell Formulas
RangeFormula
C8=WEEKNUM(A8,2)
B15=SUMIFS(\$E\$8:\$E\$9,\$C\$8:\$C\$9,"<="&B\$14,\$D\$8:\$D\$9,">="&B\$14)

Thanks alot!

I ran into another problem tho. Since its often the dates run over a year and into a new year that has the same weeknumbers it got screwed up. So i added 2 more colums that combines year and weeknumber start and year&weeknumber end. And changed "<="&B\$14 to the year&weeknumber instead. It seme to not like this.

If you add two more columns to your Start and End Table you can do this:

Excel 2010
ABCDEFGHIJK
7StartEndWeek StartWeekEndPeople needed per week to do this task
801/03/201401/06/20149223
901/01/201401/05/20141186
10
11
12
13In need of:????
14Week number12345678910
15People Required6666666699

<tbody>
</tbody>
Sheet1

Worksheet Formulas
CellFormula
C8=WEEKNUM(A8,2)
B15=SUMIFS(\$E\$8:\$E\$9,\$C\$8:\$C\$9,"<="&B\$14,\$D\$8:\$D\$9,">="&B\$14)

<tbody>
</tbody>

<tbody>
</tbody>

Yay! got it working.

Just added year as another criteria without using combine.

Thanks again, now i understand how sumifs work.

No, was celebrating too quickly. When the date range passes a year it wont get counted at all.

Show me what you have.

I'm guessing you are showing the year in the People Count??

Show me what you have.

I'm guessing you are showing the year in the People Count??

ABCDEFGHI
7StartEndWeek StartWeek EndYear/week startYearweek end
801/03/2014
 01/06/2014

<tbody>
</tbody>
922201409201422
9
 01/01/2014

<tbody>
</tbody>
 01/05/2014

<tbody>
</tbody>
118201401201418
10
11
12
13In need of???
14week number201401201402201403201404201405
15People req

<tbody>
</tbody>

The year / week start has an IF statement to add a 0 if week is < 10. My problem is that im working with text now right?

I tried using another criteria where i had year start/end. But as soon as a date range crosses a year it doesnt count anything since the weeknumbers now are smaller than the start number.

The people count is still the same. I just need it to show for the year since all my callendars run over 2 years. And in most instances atleast crosses 1 year somwhere in the date range.

Last edited:
Ok, drop the two additional columns for the weeknumber and try this:

Excel 2010
ABCDEFGHIJK
1StartEndPeople needed per week to do this task
201/03/201401/06/20143
301/01/201401/05/20146
431/12/201301/01/20141
5
6
7Week number201401201402201403201404201405201406201407201408201409201410
8People Required7666666699
Sheet1
Cell Formulas
RangeFormula
B8=SUMIFS(\$C\$2:\$C\$4,\$A\$2:\$A\$4,"<="&(VALUE(RIGHT(B\$7,2))*7+DATE(VALUE(LEFT(B\$7,4)),1,-2)),\$B\$2:\$B\$4,">="&(VALUE(RIGHT(B\$7,2))*7+DATE(VALUE(LEFT(B\$7,4)),1,-8)))

ok will try! :D

ok will try! :D

Yes got it working. Thank you for all help!

Much appreciated!

Replies
3
Views
188
Replies
7
Views
143
Replies
1
Views
319
Replies
2
Views
208
Replies
13
Views
635

1,203,061
Messages
6,053,307
Members
444,651
Latest member
markkuznetsov1

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