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

[dversloot1]

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?

 

[Jonmo1]

Try

=SUMPRODUCT(--(TEXT(Last30Days,"mmmm")="Monday"),Last30DayCOGS)/SUMPRODUCT(--(TEXT(Last30Days,"mmmm")="Monday"))

 

[umpirerob]

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?

 

[dversloot1]

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!

 

[Jonmo1]

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.