Of many Microsoft Excel Functions, COUNTIF and WEEKDAY are two very useful ones. We can use those functions so that we can easily get some information related to the day or date from our data. In this article, we will discuss COUNTIF and WEEKDAY functions with proper examples and illustrations.
3 Easy Ways to Use COUNTIF with WEEKDAY in Excel
Suppose, we have the data of a super shop which consists of the duty schedule of the staff. Here, we will discuss the use of COUNTIF and WEEKDAY in Excel using this dataset. We also added a table showing Day and Serial in the dataset. We have started the week from Monday and marked it as 1, and have accordingly marked the rest of the days.
1. Count Weekday Using COUNTIF Function in Excel
The COUNTIF Function counts the number of cells in a range that match a certain criterion. The syntax of this function is:
Here, the range is where the criteria will be assigned for counting and the criteria is the condition for which the search will be done.
This function is available in Excel 2007 and every later version of Microsoft Excel.
Now, we will use the COUNTIF function to find weekday in Excel. Suppose, you want to find how many times a certain weekday appears in a range. Here, we will use the COUNTIF function to find how many times Sunday appears in the dataset.
Let me show you the steps.
Steps:
- Firstly, select the cell where you want to determine the weekday. Here, I selected Cell D5.
- Secondly, in Cell D5 write the following formula.
=TEXT(C5,"dddd")
- Thirdly, press Enter.
- After that, drag the Fill Handle to copy the formula to the other cells.
- Now, you can see that we have copied the formula and got weekdays.
- After that, select the cell where you want to calculate the No. of Days.
- Then, write the following formula in that selected cell.
=COUNTIF(D5:D13,D15)
- Next, press Enter to get the result.
Read More: COUNTIF vs COUNTIFS in Excel
2. Apply COUNTIFS Function for Weekday with Condition in Excel
The COUNTIFS function is a statistical function. This function counts the number of times all the conditions are satisfied. The syntax of this function is:
Here, criteria_range1 is the first range to evaluate, criteria1 can be a number, text, or cell reference, and [criteria_range2, criteria2] are optional. This function allows up to 127 pairs of criteria_range and criteria within a single formula.
This function is available in Excel 2007 and onward versions of Microsoft Excel.
Now, we will solve the following problem using the COUNTIFS function in Excel. Suppose, you want to find out if the employee named Frank duty on Saturday. We will use the COUNTIFS function to check the weekday and return the result as True or False. Let’s see the steps.
Steps:
- In the beginning, determine the weekdays in the Day column following the steps from Method-01.
- Then, select the cell where you want the Outcome. Here, I selected Cell D17.
- Next, in Cell D17 write the following formula.
=IF(COUNTIFS(D5:D13,D15,B5:B13,D16),"True","False")
- Finally, press Enter to get the Outcome.
🔎 How Does the Formula Work?
- COUNTIFS(D5:D13,D15,B5:B13,D16): Here, the COUNTIFS function returns the number of cells that matches both criteria.
- IF(COUNTIFS(D5:D13,D15,B5:B13,D16),”True”,”False”): Now, the IF function checks if any cell matches both criteria. If the logical_test is TRUE then the formula returns “True”. Otherwise, it returns “False”.
3. Combine WEEKDAY Function with Other Excel Functions
The WEEKDAY function returns the day of the week corresponding to a date. The day is given as an integer, ranging from 1 (Sunday) to 7 (Saturday), by default. The syntax of this function is:
=WEEKDAY(serial_number,[return_type])Here, serial_number is a sequential number that refers to the date of the day you are searching for and return_type is a number that determines the type of return value.
This function is available in Excel 2007 and every later version of Microsoft Excel.
In this section, we will discuss the WEEKDAY function with other functions to fulfill the criteria. Here, we will use the Serial number of days. The serial number we will take as a reference is mentioned on the Dataset sheet. The WEEKDAY function compares the date with the day-wise serial number. Here, we set Day as Saturday and the Name as Frank. Now, we will determine the Outcome.
Let’s see the steps.
Steps:
- Firstly, select the cell where you want the Outcome.
- Secondly, write the following formula in that selected cell.
=IF(SUMPRODUCT((WEEKDAY(B5:B13,2)=C17)*(C5:C13=C18)),"True","False")
- Thirdly, press Enter.
🔎 How Does the Formula Work?
- WEEKDAY(B5:B13,2)=C17: Here, the formula will search C17 in the range B5:B13. If the condition satisfies the return will be True otherwise False.
- C5:C13=C18: Now, the formula will search C18 in the range C5:C13. If the condition satisfies the return will be True otherwise False.
- SUMPRODUCT((WEEKDAY(B5:B13,2)=C17)*(C5:C13=C18)): This part of the formula will return 1 if conditions satisfy, otherwise 0.
- IF(SUMPRODUCT((WEEKDAY(B5:B13,2)=C17)*(C5:C13=C18)),”True”,”False”): Now, the formula will return True if the return of SUMPRODUCT is 1 and False otherwise.
Read More: How to Use COUNTIF to Count Date Less Than Today in Excel
Practice Section
Here, we have provided a practice sheet for you to practice using the COUNTIF function with the WEEKDAY function in Excel.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
Conclusion
In this article, we explained COUNTIFS and WEEKDAY functions. Weekday function is required when we need any reference of day in number format. We hope this discussion satisfies your needs. Please place your comment in the comment box if you have any suggestions.
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
