Microsoft Office Tutorials and References

In Depth Information

{=SUM((WEEKDAY(DATE(A1,A2,ROW(INDIRECT(“1:”&DAY(DATE(A1,A2+1,0))))))=B1)*1)}

See Chapters 14 and 15 for more information about array formulas.

Figure 6-5 shows this formula used in a worksheet. In this case, the formula uses mixed cell references so that

you can copy it. For example, the formula in cell C3 is

{=SUM((WEEKDAY(DATE($B$2,$A3,ROW(INDIRECT(“1:”&DAY(DATE($B$2,$A3+1,0))))))=C$1)*1)}

Additional formulas use the SUM function to calculate the number of days per month (column J) and the num-

ber of each weekday in the year (row 15).

The workbook shown in Figure 6-5, day of the week count.xlsx, is available at this

book's website.

Figure 6-5:
Calculating the number of each weekday in each month of a year.

Expressing a date as an ordinal number