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>
<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>
<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
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/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 |
<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/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 |
<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