How to Use COUNTIF with WEEKDAY in Excel

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.

Data set for COUNTIF WEEKDAY in Excel

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.
criteria1 – This may be a number, text, or cell reference.
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.

COUNTIFS Function with Condition in Excel

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

COUNTIFS Function with Condition in Excel

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.

COUNTIFS Function with Condition in Excel

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.


Similar Readings:


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.
Return_type – A number that determines the type of return value. The returned type is 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.

Combine WEEKDAY Function With Others in Excel

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

Combine WEEKDAY Function With Others in Excel

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.

Combine WEEKDAY Function With Others in Excel

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.


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

Alok

Alok

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

Leave a reply

ExcelDemy
Logo