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.
2 Answers
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.
Consider:
=SUMPRODUCT(--(B4:B17="Thursday")*(C4:C17="John"))
You do not need WEEKDAY() because you have a column that specifically defines each day.
