Counting items within a range with column match criteria

[benlw1984]

Greetings all,

I've got a range of columns and rows (A1:I29). Column A contains dates in the month (28 days at a time) and columns B through I are used to record names of people rostered to work on those dates.

In another sheet I have a named range (PublicHolidays) of known public holiday dates for the next few years.

What I need to do, is return a single count of the number of people rostered on public holiday dates during the month. Essentially I need to check whether the date in A2 exists within the named range of public holiday dates, and if it does, count the number of items in the same row to the right of the date (i.e the number of people rostered on that day). I can do this for a single row using =IF(COUNTIF(PublicHolidays,Roster!A2),COUNTA(Roster!B2:I2),""), however I need a single total for all the rows in the range (A2:I29), not just a single row.

Any ideas?

I can't upload an example worksheet at the moment (work machine restrictions) but will do so later today if necessary.

Many thanks in advance.

Cheers

Ben

[benlw1984]

Edit - example workbook attached now.

Cheers

Ben

[JeteMc]

First of all let me say that I don't see anything wrong with using column K to get the total per row then using a formula like: =SUM(K2:K29)
For aesthetic purposes column K could be moved and/or hidden.
After all a spread sheet has 16384 columns, and the formulas are easy enough to understand that someone could modify them without too much trouble if requirements change.
That said the following also works: =SUMPRODUCT(COUNTIFS(Tables!B4:F15,A2:A29)*(B2:I29<>""))
Let us know if you have any questions.

[benlw1984]

Many thanks - that's exactly what I needed.

Cheers

Ben

[JeteMc]

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.