Using Excel formulae to filter and sum rows

Question

Hi, In reference to the example table below, how can I use Excel formulae to calculate Total Units Sold, with today's date as the starting date from which to count backwards:

Last 3 working days i.e. January 9, 8, 5
Last 7 working days i.e. January 9, 8, 5, 4, 3, 2, and December 29

Please note that I want to exclude Weekend (Sat & Sun) and public holidays.

I also need the same totals, but without excluding any days i.e.

Last 3 days i.e. 9, 8, 7
Last 7 days i.e. 9, 8, 7, 6, 5, 4, 3

I know I can use Slicers and Column filters and or Pivot Tables. But, I want the formulae so I can add a new table above the table below that shows all the above calculations without requiring the user to filter anything manually. The new table would have four rows - please see attached image.

Screenshot 2024-02-02 135241.png

Answer 1

You will need a list of holidays. The WORKDAY function will calculate the date of the N-th workday before a start date when you specify N-1. For your example, if today were 9 January 2024, the formulas

• SUMIF(A9:A20,">="&WORKDAY(TODAY(),-2,holiday-dates),B9:B20)
• SUMIF(A9:A20,">="&WORKDAY(TODAY(),-6,holiday-dates),B9:B20)

would do what you want for workdays.

For total days, the criteria is simply ">="&TODAY()-2 or ">="&TODAY()-6

Answer 2

Screenshot 2024-02-02 135241.png

The formatting of the above question went wrong and I am unable to correct it. Also, the example assumes today is 9 January 2024 (because I wanted a range with a weekend and public holiday)

The attached image shows what I am trying to create in Excel.

Answer 3

Thank you Barry :-)