## Count the number of a specific weekday in a range

Question: How to count, for example, mondays in a range?

Answer: If your range looks like this (see picture below), use =SUM(N(WEEKDAY(A1:A15)=2)) + Ctrl +Shift Enter

If your range looks like this (see picture below), use =SUM(N(WEEKDAY(ROW(INDIRECT("D4:D"&A18-A17+1))+A17-1)=4)) + Ctrl +Shift Enter

This formula creates all dates in the range and checks how are a match to 4. (Wednesday)

You can click on headers to sort table for easy finding.

### 4 Responses to “Count the number of a specific weekday in a range”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

Hi there, it seems very helpful and it works. however, i would like the range to be adapted to possible changes.

what i mean is that sometimes i would want it to start fron cell a2 and finish at call a19.

is there a way to make this connection?

aqctually i think it's better if i give you an exact description.

I am trying to make a sheet that calculates the total amount of money that has to be paid as "Easter present" to an employee. This amount depends on the day the employ started working, the day they finished and the total number of sundays between these days.

so, an employ might have started at Jan 1 and finished at April 30 (that's the maximum period). another employ might have started at feb 15 and finished at april 25.

what i'm trying to do is to be able to put those dates in 2 seperate cells, so as the given function to be able to take those dates as starting and finishing point of the range (as it looks on the 1st picture).

thanks a lot

Right now I'm using the following formula to tell me how many of a specific defined day, ie Wednessdays,fall between a start date and stop date. Here is the formula:

=SUMPRODUCT(--(ISNUMBER(MATCH(TEXT(ROW(INDIRECT(D3&":"&D4)),"dddd"),D15,0))))

The only thing is this doesn't take into account sepcific holidays that occur during the period that i dont want to include in the count. How can i adjust this so i can not include defined holidays.

Steve,

read this post:

How many of a specific weekday falls between a start date and an end date except holidays