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.

1

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.

1

Consider:

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

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

1

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.