You might wonder why we aren't using COUNTIF or COUNTIFs? These functions seem like the obvious solution. However, without adding a helper column that contains a weekday value, there is no way to create a criteria for COUNTIF to count weekdays in a range of dates.
Instead, we use the versatile SUMPRODUCT function, which handles arrays gracefully without the need to use Control + Shift + Enter.
We are using SUMPRODUCT with just one argument, which consists of this expression:
Working from the inside out, the WEEKDAY function is configured with the optional argument 2, which causes it to return numbers 1-7 for the days Monday-Sunday, respectively. This isn't strictly necessary, but it makes it easier to list the days in order with the numbers in column G in sequence.
WEEKDAY then evaluates each value in the named range "dates" and returns a number. The result is an array like this:
The numbers returned by WEEKDAY are then compared to the value in E4, which is 1.
SUMPRODUCT only works with numbers (not text or booleans) so we use the double-negative to coerce the TRUE/FALSE values to one's and zeros:
With just this single array to process, SUMPRODUCT sums the items and returns the result, 3.
Dealing with blank dates
If you have blank cells in the list of dates, you will get incorrect results, since the WEEKDAY function will return even when there is no date. To handle empty cells, you can adjust the formula as follows:
To sum data by weekday (i.e. sum by Mondays, Tuesdays, Wednesdays, etc.), you can use the SUMPRODUCT function together with the WEEKDAY function. In the example shown, the formula in H4 is: = SUMPRODUCT (( WEEKDAY ( dates , 2 ) = G4 ) * amts ) How...
To count the number of birthdays in a list, you can use a formula based on the SUMPRODUCT and MONTH functions. In the example shown, E5 contains this formula: = SUMPRODUCT ( -- ( MONTH ( birthday ) = D5 )) This formula counts birthdays in January (...
To count dates in a given year, you can use the SUMPRODUCT and YEAR functions. In the example shown, the formula in E5 is: = SUMPRODUCT ( -- ( YEAR ( dates ) = D5 )) where "dates" the a named range B5:B15. How this formula works The YEAR function...
The SUMPRODUCT function multiplies ranges or arrays together and returns the sum of products. This sounds boring, but SUMPRODUCT is an incredibly versatile function that can be used to count and sum like COUNTIFS or SUMIFS, but with more...
The Excel WEEKDAY function takes a date and returns a number between 1-7 representing the day of week. By default, WEEKDAY returns 1 for Sunday and 7 for Saturday. You can use the WEEKDAY function inside other formulas to check the day of week...
Excel Formula Training
Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.