Sumif for dynamic range of dates that equal Mondays, Tuesdays, etc.

dversloot1

Board Regular
Joined
Apr 3, 2013
Messages
113
Hello,

I've created 2 dynamic ranges:
Last30Days which captures the range of the past 30 days formatted as Short Dates.
Last30DayCOGS which captures the range of cost of goods sold on those past 30 days.

I'm trying to get the average sale by day of the week for the past 30 days.

Monday Average = Sumif(Last30Days,"Monday",Last30DayCOGS) / countif(Last30Days, "Monday")
Tuesday Average = Sumif(Last30Days,"Tuesday",Last30DayCOGS) / countif(Last30Days,"Tuesday")

Any Idea how I can have this calculated for all each day of the week?
 
Try

=SUMPRODUCT(--(TEXT(Last30Days,"mmmm")="Monday"),Last30DayCOGS)/SUMPRODUCT(--(TEXT(Last30Days,"mmmm")="Monday"))
 
Upvote 0
You can use the Weekday() function in Excel. Returns a value of 1 to 7 based on a date. Create a new Range for each day of the week, then use your formulas above?
 
Upvote 0
For some reason the Text conversion to day of the week didn't allow it to work so I slightly altered your formula to this:
Sunday = SUMPRODUCT(--(WEEKDAY(Last30Days)=1),Last30DayCOGS)/SUMPRODUCT(--(WEEKDAY(Last30Days)=1))
Monday = SUMPRODUCT(--(WEEKDAY(Last30Days)=2),Last30DayCOGS)/SUMPRODUCT(--(WEEKDAY(Last30Days)=2))
....

Thanks!
 
Upvote 0
For some reason the Text conversion to day of the week didn't allow it to work

DOH!!!
That's because I used "mmmm" for the format instead of the correct "dddd"

LOL.

Anyway, glad you got it working. The Weekday function is just as good.
 
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top