CODEWITHSUNDEEP
excelweekday
Alawna
Alawna

Reputation: 33

Using WEEKDAY function inside a COUNTIFS

I want to count the times John was in the blue room and it was a thursday. I played with COUNTIFS and WEEKDAY and checked the documentation.

I tried COUNTIFS with two conditions but got errors or 0 as a result.

Click to see the excel sheet screenshot

Upvotes: 3

Views: 9961

Answers (2)

Scott Craner
Scott Craner

Reputation: 152450

As @Gary'sStudent said with the column with the day already in place you do not need the WEEKDAY a simple COUNTIFS will work:

=COUNTIFS(B:B,"Thursday",C:C,"John")

If you did not have that column then you would need to revert to the SUMPRODUCT() formula similar to what @Gary'sStudent has:

=SUMPRODUCT((WEEKDAY(A4:A17)="Thursday")*(C4:C17="John"))

Both formulas will work, the main difference is that the COUTNIFS() is not an array formula, and therefore you are able to use full column references without detriment. The SUMPRODUCT, to minimize calc times, one would need to reference only the dataset.

Upvotes: 6

Gary's Student
Gary's Student

Reputation: 96753

Consider:

=SUMPRODUCT(--(B4:B17="Thursday")*(C4:C17="John"))

You do not need WEEKDAY() because you have a column that specifically defines each day.

Upvotes: 4

Related Questions