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.

**Table of Contents**Expand

## 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**.

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

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

**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