# Thread: How to use WeekNum in a SumIf Thanks: 0 Likes: 0

1. ## How to use WeekNum in a SumIf

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.
 9/6/19 9/7/19 9/8/19 9/9/19 9/10/19 9/11/19 9/12/19 9/13/19 9/14/19 9/15/19 9/16/19 9/17/19 9/18/19 9/19/19 9/20/19 9/21/19 9/22/19 9/23/19 9/24/19 9/25/19 127669332 148322320 149918866 92137347 40431402 140510929 155523745 16931759 12576337 182077049 82210390 26796816 19020423 22955150 47476811 76528476 21883809 34201369 36850047 67595049

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

 9/6/19 9/7/19 9/8/19 9/9/19 9/10/19 9/11/19 9/12/19 9/13/19 9/14/19 9/15/19 9/16/19 9/17/19 9/18/19 9/19/19 9/20/19 9/21/19 9/22/19 9/23/19 9/24/19 9/25/19 127669332 148322320 149918866 92137347 40431402 140510929 155523745 16931759 12576337 182077049 82210390 26796816 19020423 22955150 47476811 76528476 21883809 34201369 36850047 67595049 Date in Data =WEEKNUM(B4) 36 36 37 37 37 37 37 37 37 38 38 38 38 38 38 38 39 39 39 39 Today =WEEKNUM(TODAY()) 38 38 38 38 38 38 38 38 38 38 38 38 38 38 38 38 38 38 38 38 Last week =sumif(E6:X6,E7-1,E5:X5) 608030385

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

2. ## Re: How to use WeekNum in a SumIf

Don't think that weeknum works with an array unfortunately

3. ## Re: How to use WeekNum in a SumIf

Originally Posted by Fluff
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

4. ## Re: How to use WeekNum in a SumIf

thanks for the recommendations! I got it to work!

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

5. ## Re: How to use WeekNum in a SumIf

Originally Posted by aallaman
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.