Forum Discussion
Week to Date Calculation off of Specific Date
Hi Everyone!
I'm trying to calculate WTD sales off of the current date and can't seem to come up with a great solution. So far I'm thinking calculating Weekday() starting on Monday and using that to go through bunch of if statements would work, but are there any more elegant solutions?
I was thinking something like this:
=IF(WEEKDAY(A1)=2,SUMIF(Sales!B:B,A1,Sales!C:C),IF(WEEKDAY(A1)=3,SUMIF(Sales!B:B,A1,Sales!C:C)+SUMIF(Sales!B:B,A1-1,Sales!C:C)) and keep going ect. Sum (A1-2 + A1-1 + A1) for Wednesday and so on.
I'm trying to keep in contained in one cell and not add any calculation columns. I tried sumifs but it still becomes unwieldy. Would an array function work here somehow? I'm stumped, thank you!
It's hard to discuss without sample file which illustrates how your data is organized. As variant
=SUMIFS(Sales!C:C, Sales!B:B,">"&TODAY()-WEEKDAY(TODAY(),11), Sales!B:B,"<"&TODAY() )
7 Replies
- I've got a question about an =If calculation.
- What's your question? I might be able to offer some help!
- This is almost works, and is so much more concise! But if we are in the middle of the month, the formula needs to exclude older dates and only take the current week. That's kind of what I was thinking with starting from weekday() so I could always add from the latest Monday onward. I just read up more on using </>/= operators in the formulas like that and that's a huge help, I wasn't aware of that feature before, I'll try again some more using these. Thank you!
It's hard to discuss without sample file which illustrates how your data is organized. As variant
=SUMIFS(Sales!C:C, Sales!B:B,">"&TODAY()-WEEKDAY(TODAY(),11), Sales!B:B,"<"&TODAY() )
