## 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.

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 […]

Count Conditionally Formatted cells

This article explains how to count cells highlighted with Conditional Formatting (CF). The image above shows data in cell range […]

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 press with left mouse […]

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

This article demonstrates how to return the latest date based on a condition using formulas or a Pivot Table. The […]

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 […]

Identify overlapping date ranges

The formula in cell F6 returns TRUE if the date range on the same row overlaps another date range in […]

### 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/