Sumifs with 2 dates and weeknumber

E

ebbrey

Board Regular
Joined
Oct 16, 2010
Messages
56
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.


[TABLE="width: 500"]
<TBODY>[TR]
[TD]Start[/TD]
[TD]End[/TD]
[TD]People needed per
week to do this task[/TD]
[/TR]
[TR]
[TD]01.03.2014[/TD]
[TD]01.06.2014[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]01.01.2014[/TD]
[TD]01.05.2014[/TD]
[TD]6[/TD]
[/TR]
</TBODY>[/TABLE]

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

[TABLE="width: 500"]
<TBODY>[TR]
[TD]In need of:[/TD]
[TD]?[/TD]
[TD]?[/TD]
[TD]?[/TD]
[TD]?[/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD]Week number[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Avaiable person[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Avaiable person[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]

How shall i write this?

Thanks in advance!
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Sort by date Sort by votes
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)
 
Upvote 0
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.



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

Excel 2010
ABCDEFGHIJK
Week StartWeekEnd

<tbody>
[TD="align: center"]7[/TD]
[TD="bgcolor: #FAFAFA"]Start[/TD]
[TD="bgcolor: #FAFAFA"]End[/TD]

[TD="bgcolor: #FAFAFA"]People needed per week to do this task[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: #FAFAFA, align: right"]01/03/2014[/TD]
[TD="bgcolor: #FAFAFA, align: right"]01/06/2014[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]22[/TD]
[TD="bgcolor: #FAFAFA, align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: #FAFAFA, align: right"]01/01/2014[/TD]
[TD="bgcolor: #FAFAFA, align: right"]01/05/2014[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]18[/TD]
[TD="bgcolor: #FAFAFA, align: right"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="bgcolor: #FAFAFA"]In need of:[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]?[/TD]
[TD="bgcolor: #FAFAFA"]?[/TD]
[TD="bgcolor: #FAFAFA"]?[/TD]
[TD="bgcolor: #FAFAFA"]?[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="bgcolor: #FAFAFA"]Week number[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2[/TD]
[TD="bgcolor: #FAFAFA, align: right"]3[/TD]
[TD="bgcolor: #FAFAFA, align: right"]4[/TD]
[TD="bgcolor: #FAFAFA, align: right"]5[/TD]
[TD="bgcolor: #FAFAFA, align: right"]6[/TD]
[TD="bgcolor: #FAFAFA, align: right"]7[/TD]
[TD="bgcolor: #FAFAFA, align: right"]8[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9[/TD]
[TD="bgcolor: #FAFAFA, align: right"]10[/TD]

[TD="align: center"]15[/TD]
[TD="bgcolor: #FAFAFA"]People Required[/TD]
[TD="bgcolor: #FAFAFA, align: right"]6[/TD]
[TD="bgcolor: #FAFAFA, align: right"]6[/TD]
[TD="bgcolor: #FAFAFA, align: right"]6[/TD]
[TD="bgcolor: #FAFAFA, align: right"]6[/TD]
[TD="bgcolor: #FAFAFA, align: right"]6[/TD]
[TD="bgcolor: #FAFAFA, align: right"]6[/TD]
[TD="bgcolor: #FAFAFA, align: right"]6[/TD]
[TD="bgcolor: #FAFAFA, align: right"]6[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9[/TD]

</tbody>
Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]C8[/TH]
[TD="align: left"]=WEEKNUM(A8,2)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]B15[/TH]
[TD="align: left"]=SUMIFS($E$8:$E$9,$C$8:$C$9,"<="&B$14,$D$8:$D$9,">="&B$14)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...
 
Upvote 0
Yay! got it working.

Just added year as another criteria without using combine.

Thanks again, now i understand how sumifs work.
 
Upvote 0
No, was celebrating too quickly. When the date range passes a year it wont get counted at all.
 
Upvote 0
Show me what you have.

I'm guessing you are showing the year in the People Count??
 
Upvote 0
Comfy said:
Show me what you have.

I'm guessing you are showing the year in the People Count??
Click to expand...

[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Start[/TD]
[TD]End[/TD]
[TD]Week Start[/TD]
[TD]Week End[/TD]
[TD]Year/week start[/TD]
[TD]Yearweek end[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]01/03/2014[/TD]
[TD][TABLE="class: cms_table"]
<tbody>[TR]
[TD="bgcolor: #FAFAFA, align: right"]01/06/2014[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]9[/TD]
[TD]22[/TD]
[TD]201409[/TD]
[TD]201422[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][TABLE="class: cms_table"]
<tbody>[TR]
[TD="bgcolor: #FAFAFA, align: right"]01/01/2014[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="class: cms_table"]
<tbody>[TR]
[TD="bgcolor: #FAFAFA, align: right"]01/05/2014[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]1[/TD]
[TD]18[/TD]
[TD]201401[/TD]
[TD]201418[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]In need of[/TD]
[TD]?[/TD]
[TD]?[/TD]
[TD]?[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]week number[/TD]
[TD]201401[/TD]
[TD]201402[/TD]
[TD]201403[/TD]
[TD]201404[/TD]
[TD]201405[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]People req[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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:
Upvote 0
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)))
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question Question
Auto deleting a date column once the date has passed via VBA
Replies
4
Views
65
RMH2024
R
J
  • Question Question
Using CountIf to find total number based on 2 different cells?
Replies
1
Views
139
Asbestos_Jen
Asbestos_Jen
sakrams
  • Question Question
Excel VBA script for week number and the weekly dates (Concatenated)
Replies
8
Views
622
Cubist
Cubist
J
  • Question Question
Compare 2 Workbooks and add only the New Information to a Master Sheet
Replies
0
Views
266
jpaul123
J
B
  • Question Question
Excel VBA Searching Unique Variables to Match against Exported Schedule Files to Determine Date
Replies
1
Views
169
sijpie
sijpie

Forum statistics

Threads
1,224,249
Messages
6,177,419
Members
452,774
Latest member
Macca1962

Share this page

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
Back
Top