Mar 11 2021 12:01 PM
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!
Mar 11 2021 12:10 PM
Mar 11 2021 12:43 PM
Mar 11 2021 12:58 PM
SolutionIt'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()
)
Mar 11 2021 01:52 PM
Mar 11 2021 02:02 PM
@DKoontz you are welcome, glad it helped
Jun 22 2021 06:12 PM
Jun 22 2021 09:10 PM
Mar 11 2021 12:58 PM
SolutionIt'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()
)