Counting entries in a table which meet date criteria

I’m stuck!

I have a table with names down the left column and each working day of the year along the top row. On that table people record their working location each day, i.e. London, Birmingham, Glasgow, Holiday, Sick etc, and I need a formula that will allow me to calculate how many days in each month each person is working (so excluding holidays and sick).
COUNTIFS seemed to be the most likely solution, but I’ve spent hours on this and just can’t get it to work.

I’m basically looking for a formula which says “If the date in row 1 is in month 1 then count all the entries in the corresponding row where this formula is but only if their value is ‘London’, ‘Birmingham’ or ‘Glasgow’.”

I hope I’ve explained it clearly. I can’t upload an example as that would compromise the work security policy!

Thanks for looking. I really apprecaite any time you spend looking at this.

Chris.

Maybe (obviously untested)

=SUMPRODUCT(--(MONTH(Sheet1!A$1:ZZ$1)=Sheet2!A1)*((Sheet1!A$1:ZZ$1="London")+(Sheet1!A$1:ZZ$1="Birmingham")+(Sheet1!A$1:ZZ$1="GlasgowLondon")))

where ZZ1 is your last column (whatever column that is)

Couldn't get it to work Special-K, but then wondered if the * in the middle should have been a + and it works perfectly!

Thank you so much. As good as I think I am getting I would never have sussed that problem.

Cheers,

Chris.

My bad! You were of course right first time Special-K! + only looked liked it worked initially. I must have had something else wrong when using the *. Your code was spot on.

Many thanks again,

Chris.