How to use WeekNum in a SumIf

aallaman

New Member
Joined
Dec 4, 2009
Messages
44
Hi Excel Peoples

I've got a table with dates as headers and I want to sum up the values under those dates depending if they fell under this week or last week.
<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>
9/6/199/7/199/8/199/9/199/10/199/11/199/12/199/13/199/14/199/15/199/16/199/17/199/18/199/19/199/20/199/21/199/22/199/23/199/24/199/25/19
1276693321483223201499188669213734740431402140510929155523745169317591257633718207704982210390267968161902042322955150474768117652847621883809342013693685004767595049

<colgroup><col style="width: 100px"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"></colgroup><tbody>
</tbody>

If I create new rows that use Weeknum() to specify that weeknum value for the date in question and the current date the sumif works fine

<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>
9/6/199/7/199/8/199/9/199/10/199/11/199/12/199/13/199/14/199/15/199/16/199/17/199/18/199/19/199/20/199/21/199/22/199/23/199/24/199/25/19
1276693321483223201499188669213734740431402140510929155523745169317591257633718207704982210390267968161902042322955150474768117652847621883809342013693685004767595049
Date in Data=WEEKNUM(B4)3636373737373737373838383838383839393939
Today=WEEKNUM(TODAY())3838383838383838383838383838383838383838
Last week=sumif(E6:X6,E7-1,E5:X5)608030385

<colgroup><col style="width: 137px"><col width="150"><col width="182"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"></colgroup><tbody>
</tbody>


I'm trying to make the formula more efficient and eliminate the need to create more rows of data I only need temporarily. I've tried the following but neither work.

=sumif(E4:X4,Weeknum(4:4)=Weeknum(TODAY()),E5:X5) results in = 0
=sumif(Weeknum(E4:X4),Weeknum(TODAY()),E5:X5) results in = 0

Any recommendations on how to tweak this so it functions as expected?

Thanks
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Don't think that weeknum works with an array unfortunately
 
Upvote 0
Don't think that weeknum works with an array unfortunately

Why not just use sumifs and define it to sum between the dates that makes the desired week? So if you want to sum the current week you use today inside weekday and then subtract that from today to get the start of the week. Then you can define the end of the week by adding 5 or 7 days. Then you use these dates as the criteria in the sumifs. Quite simple and efficient
 
Upvote 0
thanks for the recommendations! I got it to work!

=sumifs(D5:W5,D4:W4,">="&today()-weekday(TODAY()-1),D4:W4,"<="&today()+(7-weekday(today())))
 
Upvote 0
thanks for the recommendations! I got it to work!

=sumifs(D5:W5,D4:W4,">="&today()-weekday(TODAY()-1),D4:W4,"<="&today()+(7-weekday(today())))

Great! For future reference I think your first solution might work if you use sumproduct instead of sumifs.
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

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