# How to Use COUNTIF with WEEKDAY in Excel (3 Easy Ways)

Get FREE Advanced Excel Exercises with Solutions!

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:

=COUNTIF(range, criteria)

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. Here, in the TEXT function, we selected C5 as the value and “dddd” as format_text. The formula returns the value in the mentioned format.
• 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. Here, in the COUNTIF function, I selected cell range D5:D13 as the range and Cell D15 as the criteria. The formula returns the number of cells in the range that match the criteria.

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:

=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)

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.

## Practice Section

Here, we have provided a practice sheet for you to practice using the COUNTIF function with the WEEKDAY function in Excel. ## 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.

## What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems. Alok Paul

Hello, this is Alok. I am working as an Excel & VBA Content Developer at Exceldemy. I want to provide solutions to various Excel-based problems. I completed my study at East West University major in Telecommunications Engineering. I love traveling, reading books, playing cricket.

We will be happy to hear your thoughts Advanced Excel Exercises with Solutions PDF  