How to use WeekNum in a SumIf

A

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>[TABLE="width: 0"]
<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>[TR]
[TD="align: right"]9/6/19[/TD]
[TD="align: right"]9/7/19[/TD]
[TD="align: right"]9/8/19[/TD]
[TD="align: right"]9/9/19[/TD]
[TD="align: right"]9/10/19[/TD]
[TD="align: right"]9/11/19[/TD]
[TD="align: right"]9/12/19[/TD]
[TD="align: right"]9/13/19[/TD]
[TD="align: right"]9/14/19[/TD]
[TD="align: right"]9/15/19[/TD]
[TD="align: right"]9/16/19[/TD]
[TD="align: right"]9/17/19[/TD]
[TD="align: right"]9/18/19[/TD]
[TD="align: right"]9/19/19[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=B7E1CD]#B7E1CD[/URL] , align: right"]9/20/19[/TD]
[TD="align: right"]9/21/19[/TD]
[TD="align: right"]9/22/19[/TD]
[TD="align: right"]9/23/19[/TD]
[TD="align: right"]9/24/19[/TD]
[TD="align: right"]9/25/19[/TD]
[/TR]
[TR]
[TD="align: right"]127669332[/TD]
[TD="align: right"]148322320[/TD]
[TD="align: right"]149918866[/TD]
[TD="align: right"]92137347[/TD]
[TD="align: right"]40431402[/TD]
[TD="align: right"]140510929[/TD]
[TD="align: right"]155523745[/TD]
[TD="align: right"]16931759[/TD]
[TD="align: right"]12576337[/TD]
[TD="align: right"]182077049[/TD]
[TD="align: right"]82210390[/TD]
[TD="align: right"]26796816[/TD]
[TD="align: right"]19020423[/TD]
[TD="align: right"]22955150[/TD]
[TD="align: right"]47476811[/TD]
[TD="align: right"]76528476[/TD]
[TD="align: right"]21883809[/TD]
[TD="align: right"]34201369[/TD]
[TD="align: right"]36850047[/TD]
[TD="align: right"]67595049[/TD]
[/TR]
</tbody>[/TABLE]

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>[TABLE="width: 0"]
<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>[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]9/6/19[/TD]
[TD="align: right"]9/7/19[/TD]
[TD="align: right"]9/8/19[/TD]
[TD="align: right"]9/9/19[/TD]
[TD="align: right"]9/10/19[/TD]
[TD="align: right"]9/11/19[/TD]
[TD="align: right"]9/12/19[/TD]
[TD="align: right"]9/13/19[/TD]
[TD="align: right"]9/14/19[/TD]
[TD="align: right"]9/15/19[/TD]
[TD="align: right"]9/16/19[/TD]
[TD="align: right"]9/17/19[/TD]
[TD="align: right"]9/18/19[/TD]
[TD="align: right"]9/19/19[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=B7E1CD]#B7E1CD[/URL] , align: right"]9/20/19[/TD]
[TD="align: right"]9/21/19[/TD]
[TD="align: right"]9/22/19[/TD]
[TD="align: right"]9/23/19[/TD]
[TD="align: right"]9/24/19[/TD]
[TD="align: right"]9/25/19[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]127669332[/TD]
[TD="align: right"]148322320[/TD]
[TD="align: right"]149918866[/TD]
[TD="align: right"]92137347[/TD]
[TD="align: right"]40431402[/TD]
[TD="align: right"]140510929[/TD]
[TD="align: right"]155523745[/TD]
[TD="align: right"]16931759[/TD]
[TD="align: right"]12576337[/TD]
[TD="align: right"]182077049[/TD]
[TD="align: right"]82210390[/TD]
[TD="align: right"]26796816[/TD]
[TD="align: right"]19020423[/TD]
[TD="align: right"]22955150[/TD]
[TD="align: right"]47476811[/TD]
[TD="align: right"]76528476[/TD]
[TD="align: right"]21883809[/TD]
[TD="align: right"]34201369[/TD]
[TD="align: right"]36850047[/TD]
[TD="align: right"]67595049[/TD]
[/TR]
[TR]
[TD]Date in Data[/TD]
[TD="bgcolor: #FFFF00"]=WEEKNUM(B4)[/TD]
[TD="align: right"]36[/TD]
[TD="align: right"]36[/TD]
[TD="align: right"]37[/TD]
[TD="align: right"]37[/TD]
[TD="align: right"]37[/TD]
[TD="align: right"]37[/TD]
[TD="align: right"]37[/TD]
[TD="align: right"]37[/TD]
[TD="align: right"]37[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]39[/TD]
[TD="align: right"]39[/TD]
[TD="align: right"]39[/TD]
[TD="align: right"]39[/TD]
[/TR]
[TR]
[TD]Today[/TD]
[TD="bgcolor: #FFFF00"]=WEEKNUM(TODAY())[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]38[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Last week[/TD]
[TD="bgcolor: #FFFF00"]=sumif(E6:X6,E7-1,E5:X5)[/TD]
[TD="align: right"]608030385[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Sort by date Sort by votes
Don't think that weeknum works with an array unfortunately
 
Upvote 0
Fluff said:
Don't think that weeknum works with an array unfortunately
Click to expand...

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
aallaman said:
thanks for the recommendations! I got it to work!

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

Great! For future reference I think your first solution might work if you use sumproduct instead of sumifs.
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Question Question
Formula Help with Count of Week Numbers for every Each Month
2
Replies
19
Views
1K
Armstrong_N14
A
T
  • Question Question
RAINFALL CESSATION DATE USING INDEX
Replies
1
Views
237
Thlama
T
staticfluids
  • Question Question
Distribute Number into Several Part (Increment) Based on Other Data (Dynamic Array)
Replies
2
Views
386
staticfluids
staticfluids
MrDB4Excel
  • Question Question
Populate Date to Another Workbook
Replies
7
Views
157
MrDB4Excel
MrDB4Excel
S
  • Solved
How to split week between month?
2
Replies
10
Views
2K
James006
James006

Forum statistics

Threads
1,223,894
Messages
6,175,252
Members
452,623
Latest member
Techenthusiast

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