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.

**Download Practice Workbook**

Download this practice workbook to exercise while you are reading this article.

**2 Methods with ****COUNTIF and 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. We add a table showing **Day **and** Serial **in the data set. We have started the week from **Monday **and marked it as **1**, and have accordingly marked the rest of the days.

**1. Use of COUNTIFS Function with Conditions in Excel**

**The COUNTIFS function** is a statistical function. This function counts the number of times if all the conditions are satisfied.

**Syntax:**

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

**Arguments:**

** criteria_range1** – This is the first range to evaluate.

**– This may be a number, text, or cell reference.**

*criteria1***criteria_range2, criteria2,..**

**–**These are optional. This function allows up to 127 pairs within a single formula.

In this section, we will use the **COUNTIFS **function. It will satisfy some conditions.

**Step 1:**

- Add a column mentioning the
**Day**in the data set.

**Step 2:**

- We set two criteria. One is
**Day**and the other is**Name**.

**Step 3:**

- Now, write the formula in
**Cell D18**. - For the argument section, we select
**Friday**and**Frank**from the**Day**and**Name**box. So, the formula becomes:

`=IF(COUNTIFS(C5:C13,D16,D5:D13,D17),"True","False")`

**Step 4:**

- Now, press
**Enter**.

The Outcome is showing False because Frank is not working on Friday. It’s an **AND** operation.

We replace Friday by Saturday and apply the same operation and see what is happening.

**Step 5:**

- Modify the Day box by Saturday.
- Then press
**Enter**.

The outcome is **True **as Frank is working on Saturday. The Formula is showing **True **& **False **in return as we set the **IF function** with these two conditions.

**Read More:** **COUNTIF vs COUNTIFS in Excel (4 Examples)**

**Similar Readings**

**VBA COUNTIF Function in Excel (6 Examples)****How to Use COUNTIF Between Two Numbers (4 Methods)****COUNTIF Excel Example (22 Examples)****COUNTIF Date Is within 7 Days****How to Use Excel COUNTIF That Does Not Contain Multiple Criteria**

**2. Combine the 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.

**Syntax:**

**WEEKDAY(serial_number,[return_type])**

**Argument:**

** Serial_number** – A sequential number that refers to the date of the day you are searching for. Dates should be entered by using the

**DATE**function, or as a result of other formulas or functions. Problems can occur if dates are entered as text.

**– A number that determines the type of return value. The returned type is**

*Return_type***Number**.

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 reference is mentioned on the **Data Set** sheet. The **WEEKDAY **function compares the date with the day-wise serial number.

**Step 1:**

- In the criteria section, we set
**Day**as Saturday and**Name**as Frank. - The corresponding serial of Saturday is taken from the data set sheet.

**Step 2:**

- Now, write the formula in
**cell D19**. So, the formula is:

`=IF(SUMPRODUCT((WEEKDAY(B5:B13,2)=D17)*(D5:D13=D18)),"True","False")`

**Step 3:**

- Press the
**Enter**.

The outcome is **True** because from the data we see that Frank worked on Saturday.

Now, change any of the references and see what happens.

**Step 4:**

- Replace Frank with Jose.
- Press the
**Enter**.

The outcome is **False**, as Jose is not working on Saturday. It performs **AND **functionality.

**Formula Breakdown:**

**WEEKDAY(B5:B13,2)=D17**

This function will search **D17** in the range **B5:B13. **If the condition satisfies return will be **True **otherwise **False**.

**D5:D13=D18**

This function will search **D18** in the range **D5:D13. **If the condition satisfies return will be **True **otherwise **False**.

**SUMPRODUCT((WEEKDAY(B5:B13,2)=D17)*(D5:D13=D18))**

This function will return **1 if **conditions satisfy, otherwise **0**.

**IF(SUMPRODUCT((WEEKDAY(B5:B13,2)=D17)*(D5:D13=D18)),”True”,”False”)**

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

**Conclusion**

In this article, we explained **COUNTIFS **and **WEEKDAY** functions. Weekday function is required when we need any reference of day in number format. I 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 (4 Ways)****COUNTIF Between Two Dates in Excel (4 Suitable Examples)****How to Use COUNTIF for Date Range in Excel (6 Suitable Approaches)****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 (2 Examples)**