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:

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

Answer:

Array formula in cell B12:

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

Array formula in cell B13:

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

How to create an array formula

  1. Select cell B12
  2. Copy (Ctrl + c) above formula
  3. Paste (Ctrl + v) in formula bar
  4. Press and hold Ctrl + Shift
  5. Press Enter once
  6. 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

How many of a specific weekday falls between a start date and an end date.xlsx