Let's say that I have a three column table/range for all of 2022: date, Day of Week, and Sales. I want to be able to calculate the average sales for a Monday, Tuesday, etc. Using Visual Basic, how would I pull sales for every Monday in 2022 into the Average() function?

CodePudding user response：

As Scott mentioned, Averageif, or Averageifs to test multiple criteria.

Example of Averageif for only DoW:
=AVERAGEIF(B:B,E2,C:C)

Example of Averageifs for Dow and Year
=AVERAGEIFS(D:D,B:B,G2,C:C,F2)

Other options include pivot table

This is just a quick one:

CodePudding user response：

Playing With Dates (Excel Formula)

• If you have Microsoft 365, in cell F2 you could use:

  =LET(MyArr,(TEXT($A$2:$A$33,"dddd")=$E2)*(YEAR($A$2:$A$33)=F$1)*$C$2:$C$33,
      AVERAGE(FILTER(MyArr,MyArr<>0)))
  

  and copy to the right and down (F2:G8).

• The days are in E2:E8 and the years e.g. 2021 and 2022, are in F1:G1.

• This only uses the Date and Sales columns.