Tom’s Tutorials For Excel: Summing Historic Numbers by Date, Excluding Weekends or Weekdays

Tom’s Tutorials For Excel: Summing Historic Numbers by Date, Excluding Weekends or Weekdays
When you have a list of numbers for previous dates, such as with payroll or sales activity, here is an example of how you can sum the past 14 days for weekdays only and for weekends only. You can compile any count of previous days by changing the number 14 in the formula to some other count of days.
The first picture shows the sum of Sales activity for the past 14 days, excluding weekend dates, for this example’s current date of April 3, 2017 in cell F1. Cell F2 is shaded yellow, returning $5,444 from this formula:
=SUMPRODUCT((WEEKDAY(OFFSET(A2,MATCH(MAX(A2:A367),A2:A367,0)-1,,-14),2)<6)*(OFFSET(A2,MATCH(MAX(A2:A367),A2:A367,0)-1,1,-14)))

The second picture shows the same period of time, with cell F3 shaded blue and returning $2,116 for just the weekends, from this formula:
=SUMPRODUCT((WEEKDAY(OFFSET(A2,MATCH(MAX(A2:A367),A2:A367,0)-1,,-14),2)>5)*(OFFSET(A2,MATCH(MAX(A2:A367),A2:A367,0)-1,1,-14)))