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.

countif weekday


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.

Dataset for Using COUNTIF with WEEKDAY


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.

Dataset for Using COUNTIF to Calculate the Number of Weekday

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")

Using TEXT Function to Determine Weekday

  • Thirdly, press Enter.

Getting Weekday from TEXT Function

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.

Dragging Fill Handle to Copy Formula

  • Now, you can see that we have copied the formula and got weekdays.

Final Output from Text Function

  • 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)

Counting Weekday with COUNTIF Function in Excel

  • Next, press Enter to get the result.

Getting Result from COUNTIF Function

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.

Dataset for Employing COUNTIFS Function

  • 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")

Applying COUNTIFS Function for Weekday with Condition

  • Finally, press Enter to get the Outcome.

Getting Outcome from COUNTIFS Function

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

Dataset for Employing COUNTIFS Function

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")

Combining WEEKDAY Function with Other Excel Functions

  • Thirdly, press Enter.

Getting Outcome from Weekday Function

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

Practice Section for Using COUNTIF with WEEKDAY


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

What is ExcelDemy?

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

Alok Paul has completed his B.Sc. in Electronics and Telecommunication Engineering from East West University. He has been working on the ExcelDemy project for more than 2 years. He has written 220+ articles and replied to numerous comments. He is experienced in Microsoft Office, especially in Excel. He also led some teams on Excel and VBA content development. He has a keen interest in Advanced Excel, Data analysis, Excel Pivot Table, Charts, and Dashboard. He loves to research... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo