Sumifs with 2 dates and weeknumber

[ebbrey]

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?

Thanks in advance!

 

[Comfy]

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

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K
7	Start	End	Week Start	WeekEnd	People needed perweek to do this task
8	01/03/2014	01/06/2014	9	22	3
9	01/01/2014	01/05/2014	1	18	6
10
11
12
13	In need of:		?	?	?	?
14	Week number	1	2	3	4	5	6	7	8	9	10
15	People Required	6	6	6	6	6	6	6	6	9	9
Sheet1

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

 

[ebbrey]

Yeah i had these colums already, great!!

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

	A	B	C	D	E	F	G	H	I	J	K
7	Start	End	Week Start	WeekEnd	People needed per week to do this task
8	01/03/2014	01/06/2014	9	22	3
9	01/01/2014	01/05/2014	1	18	6
10
11
12
13	In need of:		?	?	?	?
14	Week number	1	2	3	4	5	6	7	8	9	10
15	People Required	6	6	6	6	6	6	6	6	9	9

<tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula 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>

 

[ebbrey]

Yay! got it working.

Just added year as another criteria without using combine.

Thanks again, now i understand how sumifs work.

 

[ebbrey]

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

 

[Comfy]

Show me what you have.

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

 

[ebbrey]

Show me what you have.

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

	A	B	C	D	E	F	G	H	I
7	Start	End	Week Start	Week End	Year/week start	Yearweek end
8	01/03/2014	01/06/2014 <tbody> </tbody>	9	22	201409	201422
9	01/01/2014 <tbody> </tbody>	01/05/2014 <tbody> </tbody>	1	18	201401	201418
10
11
12
13	In need of	?	?	?
14	week number	201401	201402	201403	201404	201405
15	People 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.

 

[Comfy]

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

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K
1	Start	End	People needed per week to do this task
2	01/03/2014	01/06/2014	3
3	01/01/2014	01/05/2014	6
4	31/12/2013	01/01/2014	1
5
6
7	Week number	201401	201402	201403	201404	201405	201406	201407	201408	201409	201410
8	People Required	7	6	6	6	6	6	6	6	9	9
Sheet1

Cell Formulas
Range	Formula
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)))

 

[ebbrey]

ok will try! :D

 

[ebbrey]

ok will try! :D

Yes got it working. Thank you for all help!

Much appreciated!