excelvbaif-statementexcel-formulacount

Count cells with two conditions


We have a yearly calendar. https://ibb.co/8NcPM1y

In C10:C381 there are the weekdays (Monday-Sunday).
In columns E-W we have our employees.

The employees put an "18" in their cell if they work the late shift.
There is only one "18" per row (meaning only 1 employee works late every day)

My task is to count how often each employee works the late shift in a year.

I want to check Column C for "Friday".
If yes, check the corresponding row if there is an "18" in a cell.
If yes, count up for that employee/column.

I tried something similar to this:

=IF(ISNUMBER(SEARCH("Friday", C10:C381)), IF(ISNUMBER(SEARCH("18", INDEX(C10:C381, MATCH("Friday", C10:C381, 0)))), INDEX(C10:C381, MATCH("Freitag", C10:C381, 0)-8), ""), "")

Solution

  • Pls. try this enter image description here

    Copy function under all the WorkerX names.

    Modify D3:D20 and C3:C20 according to the required size. Change Friday to the dayname which is required, or add additional daynames in the same mood. (C3:C20; "dayname")