Excel: Count weekdays - with this function it works

If you want to count in Microsoft Excel how many weekdays are within a date list, it works with the help of several functions that you have to combine. Learn here how to proceed to count weekdays.

How to count weekdays using a matrix formula

With a matrix formula, which is composed of a combination of the SUM, WEEKDAY and IF functions, you have the possibility to count weekdays within a date list.The syntax of the combination looks like this:

=SUM(IF(WEEKDAY(range;type)<=weekdays;1;0))

• You use the "range" argument to specify the cell range in which Excel should count the data.
• The "type" argument defines how the weekdays should be numbered. By default, the function uses type 1, which defines Sunday as day 1 and Saturday as day 7. For counting weekdays, type 2 is recommended, because there Monday is defined as day 1 and Sunday as day 7.
• About the argument "weekdays" you specify which days should be counted or not counted. If you enter <=5 here, Excel will count all values from 1 to 5 inclusive, which means that all days except Saturday and Sunday will be recorded.

If you want Excel to count all weekdays (Monday to Friday inclusive) within a date list in the cell range A2 to A16, the formula looks like this: =SUM(IF(WEEKDAY(A2:A16;2)<=5;1;0)). Note that you must close the formula with the key combination [CTRL] + [SHIFT] + [ENTER] for it to be applied as a matrix formula.