Using WEEKDAY function inside a COUNTIFS

Question

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

Tried COUNTIFS with two conditions but got errors or 0 as a result. — Alawna, May 15 '16 at 14:53

Scott Craner · Accepted Answer · 2016-05-15T15:11:13.337

5

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.

edited May 15 '16 at 15:11

answered May 15 '16 at 14:51

Scott Craner

148,073
10
49
81

I just learned that you could day of the week check using the days name in text instead of a number 1-7. I assume that means that will work for MONTH() too. – Forward Ed May 15 '16 at 18:29

score 4 · Answer 2 · answered May 15 '16 at 14:29

4

Consider:

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

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

answered May 15 '16 at 14:29

Gary's Student

95,722
10
59
99

1

@Alawna Please note that Scott's answer is better than mine. – Gary's Student May 15 '16 at 14:53

Using WEEKDAY function inside a COUNTIFS

2 Answers2