Skip to content
xlsoffice. All Rights Reserved
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Lookup and Reference Examples

  • Count rows that contain specific values in Excel
  • Two-column Lookup in Excel
  • How to get address of named range in Excel
  • Basic INDEX MATCH approximate in Excel
  • Count unique text values with criteria

Data Analysis Examples

  • Add Outline to Data in Excel
  • Reverse List in Excel
  • Filter Data Based on Date in Excel
  • Conditional Formatting Icon Sets Examples in Excel
  • Excel Frequency Function Example

Data Validation Examples

  • Excel Data validation must contain specific text
  • Excel Data validation date in specific year
  • Excel Data validation require unique number
  • Excel Data validation number multiple 100
  • Excel Data validation exists in list

Count holidays between two dates in Excel

by

This tutorial shows how to Count holidays between two dates in Excel using example below.

To count holidays that occur between two dates, you can use the SUMPRODUCT function.

 Formula

=SUMPRODUCT((holidays>=start)*(holidays<=end))

Explanation

In the example shown, the formula in F8 is:

=SUMPRODUCT((B4:B12>=F5)*(B4:B12<=F6))

How this formula works

This formula uses two expressions in a single array inside the SUMPRODUCT function.

Worked Example:   How to check if cell contains all of many things in Excel

The first expression tests every holiday date to see if it’s greater than or equal to the start date in F5:

(B4:B12>=F5)

This returns an array of TRUE/FALSE values like this:

{FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE}

The second expression tests every holiday date to see if it’s less than or equal to the end date in F6:

(B4:B12<=F6)

which returns an array of TRUE/FALSE values like this:

Worked Example:   Sum formulas only in Excel

{TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE}

The multiplication of these two arrays automatically coerces the TRUE/FALSE values to ones and zeros, resulting in arrays that look like this:

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

After multiplication, we have just one array like this:

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

Finally, SUMPRODUCT sums the items in the array and returns 4.

Worked Example:   Count dates by day of week in Excel

Holidays on weekdays only

To count holidays that occur on weekdays only (Mon-Fri), you can extend the formula like this:

=SUMPRODUCT((range>=F5)*(range<=F6)*(WEEKDAY(range,2)<6))

Post navigation

Previous Post:

Count day of week between dates in Excel

Next Post:

Count times in a specific range in Excel

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others
  • How to check cell contains which things in Excel
  • How to calculate Number of Instances in Excel
  • ASC function: Description, Usage, Syntax, Examples and Explanation
  • Find most frequent text within a range with criteria in Excel
  • How to convert text string to array in Excel
  • Convert decimal minutes to Excel time
  • Get week number from date in Excel
  • Convert date string to date time in Excel
  • How to get Weekdays, Working days between Two Dates in Excel
  • How to calculate working days left in month in Excel
  • DISC function: Description, Usage, Syntax, Examples and Explanation
  • EFFECT function: Description, Usage, Syntax, Examples and Explanation
  • Calculate interest rate for loan in Excel
  • Calculate payment periods for loan in Excel
  • SYD function: Description, Usage, Syntax, Examples and Explanation
© 2022 xlsoffice . All Right Reserved. | Teal Smiles