## Count weekday within date range except holidays

**Steve asks:**

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:

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.

**Answer:**

Array formula in cell B12:

Array formula in cell B13:

**How to create an array formula**

- Select cell B12
- Copy (Ctrl + c) above formula
- Paste (Ctrl + v) in formula bar
- Press and hold Ctrl + Shift
- Press Enter once
- Release all keys

### Explaining array formula in cell B12

#### Step 1 - Create dates

ROW(INDIRECT(B4&":"&B5))

becomes

ROW(INDIRECT(40544&":"&40603))

becomes

ROW(40544:40603)

and returns

{40544; 40545; 40546; 40547; 40548; 40549; 40550; 40551; 40552; 40553; 40554; 40555; 40556; 40557; 40558; 40559; 40560; 40561; 40562; 40563; 40564; 40565; 40566; 40567; 40568; 40569; 40570; 40571; 40572; 40573; 40574; 40575; 40576; 40577; 40578; 40579; 40580; 40581; 40582; 40583; 40584; 40585; 40586; 40587; 40588; 40589; 40590; 40591; 40592; 40593; 40594; 40595; 40596; 40597; 40598; 40599; 40600; 40601; 40602; 40603}

#### Step - 2 Check if date is a holiday

MATCH(ROW(INDIRECT(B4&":"&B5)), $B$8:$B$9, 0)

becomes

MATCH({40544; 40545; 40546; 40547; 40548; 40549; 40550; 40551; 40552; 40553; 40554; 40555; 40556; 40557; 40558; 40559; 40560; 40561; 40562; 40563; 40564; 40565; 40566; 40567; 40568; 40569; 40570; 40571; 40572; 40573; 40574; 40575; 40576; 40577; 40578; 40579; 40580; 40581; 40582; 40583; 40584; 40585; 40586; 40587; 40588; 40589; 40590; 40591; 40592; 40593; 40594; 40595; 40596; 40597; 40598; 40599; 40600; 40601; 40602; 40603}, $B$8:$B$9, 0)

becomes

MATCH({40544; 40545; 40546; 40547; 40548; 40549; 40550; 40551; 40552; 40553; 40554; 40555; 40556; 40557; 40558; 40559; 40560; 40561; 40562; 40563; 40564; 40565; 40566; 40567; 40568; 40569; 40570; 40571; 40572; 40573; 40574; 40575; 40576; 40577; 40578; 40579; 40580; 40581; 40582; 40583; 40584; 40585; 40586; 40587; 40588; 40589; 40590; 40591; 40592; 40593; 40594; 40595; 40596; 40597; 40598; 40599; 40600; 40601; 40602; 40603}, {40548; 40555}, 0)

and returns

{#N/A; #N/A; #N/A; #N/A; 1; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; 2; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A}

#### Step 3 - Filter non holiday dates

IF(ISERROR(MATCH(ROW(INDIRECT(B4&":"&B5)), $B$8:$B$9, 0)), ROW(INDIRECT(B4&":"&B5)), "")

becomes

IF(ISERROR({#N/A; #N/A; #N/A; #N/A; 1; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; 2; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A}, ROW(INDIRECT(B4&":"&B5)), "")

becomes

IF(ISERROR({#N/A; #N/A; #N/A; #N/A; 1; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; 2; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A}, {40544; 40545; 40546; 40547; 40548; 40549; 40550; 40551; 40552; 40553; 40554; 40555; 40556; 40557; 40558; 40559; 40560; 40561; 40562; 40563; 40564; 40565; 40566; 40567; 40568; 40569; 40570; 40571; 40572; 40573; 40574; 40575; 40576; 40577; 40578; 40579; 40580; 40581; 40582; 40583; 40584; 40585; 40586; 40587; 40588; 40589; 40590; 40591; 40592; 40593; 40594; 40595; 40596; 40597; 40598; 40599; 40600; 40601; 40602; 40603}, "")

and returns

{40544; 40545; 40546; 40547; ""; 40549; 40550; 40551; 40552; 40553; 40554; ""; 40556; 40557; 40558; 40559; 40560; 40561; 40562; 40563; 40564; 40565; 40566; 40567; 40568; 40569; 40570; 40571; 40572; 40573; 40574; 40575; 40576; 40577; 40578; 40579; 40580; 40581; 40582; 40583; 40584; 40585; 40586; 40587; 40588; 40589; 40590; 40591; 40592; 40593; 40594; 40595; 40596; 40597; 40598; 40599; 40600; 40601; 40602; 40603}

#### Step 4 - Convert dates to weekdays

MATCH(TEXT(IF(ISERROR(MATCH(ROW(INDIRECT(B4&":"&B5)), $B$8:$B$9, 0)), ROW(INDIRECT(B4&":"&B5)), ""), "dddd"), B6, 0)

becomes

MATCH(TEXT({40544; 40545; 40546; 40547; ""; 40549; 40550; 40551; 40552; 40553; 40554; ""; 40556; 40557; 40558; 40559; 40560; 40561; 40562; 40563; 40564; 40565; 40566; 40567; 40568; 40569; 40570; 40571; 40572; 40573; 40574; 40575; 40576; 40577; 40578; 40579; 40580; 40581; 40582; 40583; 40584; 40585; 40586; 40587; 40588; 40589; 40590; 40591; 40592; 40593; 40594; 40595; 40596; 40597; 40598; 40599; 40600; 40601; 40602; 40603}, "dddd"), B6, 0)

becomes

MATCH(TEXT({40544; 40545; 40546; 40547; ""; 40549; 40550; 40551; 40552; 40553; 40554; ""; 40556; 40557; 40558; 40559; 40560; 40561; 40562; 40563; 40564; 40565; 40566; 40567; 40568; 40569; 40570; 40571; 40572; 40573; 40574; 40575; 40576; 40577; 40578; 40579; 40580; 40581; 40582; 40583; 40584; 40585; 40586; 40587; 40588; 40589; 40590; 40591; 40592; 40593; 40594; 40595; 40596; 40597; 40598; 40599; 40600; 40601; 40602; 40603}, "dddd"), "wednesday", 0)

becomes

MATCH({"saturday"; "sunday"; "monday"; "tuesday"; ""; "thursday"; "friday"; "saturday"; "sunday"; "monday"; "tuesday"; ""; "thursday"; "friday"; "saturday"; "sunday"; "monday"; "tuesday"; "wednesday"; "thursday"; "friday"; "saturday"; "sunday"; "monday"; "tuesday"; "wednesday"; "thursday"; "friday"; "saturday"; "sunday"; "monday"; "tuesday"; "wednesday"; "thursday"; "friday"; "saturday"; "sunday"; "monday"; "tuesday"; "wednesday"; "thursday"; "friday"; "saturday"; "sunday"; "monday"; "tuesday"; "wednesday"; "thursday"; "friday"; "saturday"; "sunday"; "monday"; "tuesday"; "wednesday"; "thursday"; "friday"; "saturday"; "sunday"; "monday"; "tuesday"}, "wednesday", 0)

returns

{#N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; 1; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; 1; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; 1; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; 1; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; 1; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; 1; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A}

#### Step 5 - Count weekdays

=SUMPRODUCT(--(ISNUMBER(MATCH(TEXT(IF(ISERROR(MATCH(ROW(INDIRECT(B4&":"&B5)), $B$8:$B$9, 0)), ROW(INDIRECT(B4&":"&B5)), ""), "dddd"), B6, 0))))

becomes

=SUMPRODUCT(--(ISNUMBER({#N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; 1; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; 1; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; 1; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; 1; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; 1; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; 1; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A})))

becomes

=SUMPRODUCT(--({FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}))

becomes

=SUMPRODUCT(--({FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}))

becomes

=SUMPRODUCT({0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 1; 0; 0; 0; 0; 0; 0; 1; 0; 0; 0; 0; 0; 0; 1; 0; 0; 0; 0; 0; 0; 1; 0; 0; 0; 0; 0; 0; 1; 0; 0; 0; 0; 0; 0; 1; 0; 0; 0; 0; 0; 0})

and returns 6 in cell B12.

### Download Excel file through email

Enter your email address to receive the workbook. Note, an email will actually be sent to you.Count unique distinct values that meet multiple criteria

This post demonstrates how to build an array formula that counts unique distinct values based on criteria. What's on this […]

This article describes how to count unique distinct values. What are unique distinct values? They are all values but duplicates are […]

Count cells between specified values

This article demonstrates formulas that calculate the number of cells between two values, the first scenario involves two search values […]

Count unique distinct values in two columns

Formula in C12: =SUM(1/COUNTIF($B$3:$B$8, $B$3:$B$8))+SUM(IF(COUNTIF($B$3:$B$8, $D$3:$D$8)=0, 1/COUNTIF($D$3:$D$8, $D$3:$D$8), 0)) How to create an array formula Double click on cell C12 […]

Count unique distinct values in a filtered Excel defined Table

A few days ago Debra Dalgleish described how to create a Line Between Dates in Filtered List. She modified a […]

Question: I am trying to create an excel spreadsheet that has a date range. Example: Cell A1 1/4/2009-1/10/2009 Cell B1 […]

Find latest date based on a condition

Table of contents Lookup a value and find max date How to enter an array formula Explaining array formula Download […]

Formula for matching a date within a date range

Table of contents Match a date when a date range is entered in a single cell Match a date when […]

The image above demonstrates an array formula in cell E4 that searches for the closest date in column A to the […]

Highlight records based on overlapping date ranges and a condition

adam asks: Hi, I have a situation where I want to count if this value is duplicate and if it […]

### 2 Responses to “Count weekday within date range except holidays”

### Leave a Reply

### How to comment

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

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

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

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

Put your VBA code here.

[/vb]

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

Upload picture to postimage.org or imgur

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form

Hi Oscar... I'm trying to adapt your formula to determine in a date range of arrival dates, how many people I will have in a determine date. For example if I have 2 people arriving 05/14/17 and leaving 05/16/17 but also have 3 people arriving 05/15/17 leaving 05/17/18.

I need to know how many people I have in house for each day... 14 - 2 guests, 15-5 guests, etc.

Any ideas?

Thanks in advance!

Juan Gonzalez

I believe you are looking for this formula:

https://www.get-digital-help.com/2015/08/12/count-overlapping-days-across-multiple-date-ranges/