Summing/Averaging Totals Based off Date Criteria

[KoolKatelyn]

Hello,

I'm looking for both a sum and average formula that calculates weekly totals. (I attached my Excel document to better show what I am looking for)

I'm trying to get the sum and average of In Store Returns from the previous week (Monday through Friday) and am not sure if using a SUMIFS function that looks for the previous weeks data would be possible?

Any help with this would be much appreciated!

Thank you

[kvsrinivasamurthy]

In N2, then drag Horizontaly.

=SUMIFS($B$3:$K$3,$B$2:$K$2,">="&N$1,$B$2:$K$2,"<="&N$1+4)

In N2, then drag Horizontaly.

=IFERROR(SUMIFS($B$3:$K$3,$B$2:$K$2,">="&N$1,$B$2:$K$2,"<="&N$1+4)/SUMIFS($B$5:$K$5,$B$2:$K$2,">="&N$1,$B$2:$K$2,"<="&N$1+4),"")

[KoolKatelyn]

kvsrinivasamurthy - I believe your formula will work for what I am searching for! Thank you so much for your help!!

[kvsrinivasamurthy]

Welcome.Glad to help.

[KoolKatelyn]

Welcome.Glad to help.

Is there a way to use this same formula, except instead of weekly totals (Monday-Friday) I would use it to sum up the months totals?

At the end of the formula you used: "<="&N$1+4)

Obviously the formula is looking for the first Monday of each week and summing it up with the next 4 days of that week.

Instead of using the +4 approach, is there a way where the formula can be altered to get the entire months totals?

Something like this:

=IFERROR(SUMIFS($B$3:$K$3,$B$2:$K$2,">="&N$1,$B$2:$K$2,"<="&MONTH(N$1)/SUMIFS($B$5:$K$5,$B$2:$K$2,">="&N$1,$B$2:$K$2,"<="&MONTH(N$1),"")