Dataset Overview
Let’s say we have data from a supermarket that includes the staff’s duty schedule. In this example, we’ll explore how to use the COUNTIF and WEEKDAY functions in Excel based on this dataset. Additionally, we’ve included a table showing the days and their corresponding serial numbers. We’ll assume that the week starts on Monday (marked as 1) and proceed accordingly.
Method 1 – Count Weekday Using the COUNTIF Function
The COUNTIF function counts the number of cells in a range that match a specific criterion. Its syntax is as follows:
- range: The cell range where the criterion will be applied for counting.
- criteria: The condition for which the search will be performed.
This function has been available in Excel since version 2007.
Steps:
- Select the cell where you want to determine the weekday (e.g., Cell D5).
- In Cell D5, enter the following formula:
=TEXT(C5,"dddd")-
- This formula uses the TEXT function, with C5 as the value and “dddd” as the format_text. It returns the day of the week in the specified format.
- Press Enter.
- Drag the Fill Handle to copy the formula to other cells.
- The weekdays are displayed.
- To calculate the total number of a specific weekday (e.g., Sunday), select the cell where you want the result and enter the following formula:
=COUNTIF(D5:D13,D15)In this formula:
- D5:D13 represents the cell range.
- D15 is the criteria (Sunday in this case).
- Press Enter to get the result.
Read More: COUNTIF vs COUNTIFS in Excel
Method 2 – Using the COUNTIFS Function for Weekdays with Conditions
The COUNTIFS function is a statistical function that counts the number of times all specified conditions are met. Its syntax is as follows:
Here:
- criteria_range1: The first range to evaluate.
- criteria1: Can be a number, text, or cell reference.
- [criteria_range2, criteria2]: Optional additional criteria pairs (up to 127 pairs).
This function has been available in Excel since version 2007.
Example Problem:
Suppose we want to determine if an employee named Frank worked on a Saturday. We’ll use the COUNTIFS function to check the weekday and return the result as either True or False.
Steps:
- Determine the weekdays in the Day column using the steps from Method 1.
- Select the cell where you want the outcome (e.g., Cell D17).
- In Cell D17, enter the following formula:
=IF(COUNTIFS(D5:D13,D15,B5:B13,D16),"True","False")Here:
-
- The COUNTIFS function checks if any cell matches both criteria (D15 for the weekday and D16 for the employee’s name).
- If the logical test is TRUE, the formula returns True; otherwise, it returns False.
- Press Enter to get the Outcome.
Method 3 – Combine the WEEKDAY Function with Other Excel Functions
The WEEKDAY function in Excel returns the day of the week corresponding to a given date. By default, it provides an integer value ranging from 1 (Sunday) to 7 (Saturday). The syntax for this function is as follows:
=WEEKDAY(serial_number,[return_type])Here:
- serial_number: A sequential number representing the date you want to analyze.
- return_type: A number that determines the type of return value.
This function has been available in Excel since version 2007.
In this section, we’ll explore how to use the WEEKDAY function in combination with other functions to meet specific criteria. We’ll refer to the serial number of days provided in the dataset. The WEEKDAY function will compare the date with the day-wise serial number. Let’s assume we’re interested in checking if a specific day (e.g., Saturday) corresponds to a particular employee (e.g., Frank). Here are the steps:
Steps:
- Select the cell where you want to display the outcome.
- Enter the following formula in that selected cell:
=IF(SUMPRODUCT((WEEKDAY(B5:B13,2)=C17)*(C5:C13=C18)),"True","False")Here:
- B5:B13 represents the range of dates.
- C17 corresponds to the day (e.g., Saturday).
- C5:C13 represents the range of employee names.
- C18 corresponds to the specific employee (e.g., Frank).
- Press Enter.
How Does the Formula Work?
- WEEKDAY(B5:B13, 2) = C17: This part of the formula checks if the day of the week for each date in the range matches the specified day (Saturday). If the condition is met, it returns True; otherwise, it returns False.
- C5:C13 = C18: Similarly, this part checks if the employee’s name matches the specified name (Frank).
- SUMPRODUCT((WEEKDAY(B5:B13, 2) = C17) * (C5:C13 = C18)): The entire formula evaluates to 1 if both conditions are satisfied; otherwise, it evaluates to 0.
- IF(SUMPRODUCT(…), “True”, “False”): Finally, the IF function returns True if the sum of products is 1 (i.e., both conditions are met) and False otherwise.
Practice Section
We have provided a practice sheet for you to practice using the COUNTIF function with the WEEKDAY function in Excel.
Download Practice Workbook
You can download the practice workbook from here:
Related Articles
- How to Compare Two Columns Using COUNTIF Function
- COUNTIF Between Two Dates in Excel
- How to Use COUNTIF for Date Range in Excel
- Apply COUNTIF Function in Multiple Ranges for Same Criteria
- How to Use COUNTIF for Non Contiguous Range in Excel
- Count Text at Start with COUNTIF & LEFT Functions in Excel
- How to Use Excel COUNTIF Between Time Range
