Week to Date Calculation off of Specific Date

[DKoontz]

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!

[#2]

@DKoontz 

Perhaps

SUMIF(Sales!B:B,"<" & A1,Sales!C:C)

[#3]

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!

[#4 · Accepted solution]

@DKoontz 

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()
)

[#5]

This is perfect! I changed the last "<" to "<=" and its working perfectly. Thank you so much, I've been looking for a solution for so long.

[#6]

@DKoontz you are welcome, glad it helped

[#7]

I've got a question about an =If calculation.

[#8]

What's your question? I might be able to offer some help!