Count instances of text where cell matches month/year

[slam]

I am trying to prepare holiday data for an import into a resource planning system at work. Human Resources have provided me a spreadsheet of all our sites (100+) and which holidays each site observes from 2019 through to the end of 2025.

Sites are listed in column A. The date of the holiday observed is in row 1 (in the format dd-mmm-yyyy). Then each row lists the name of the holiday in B2, C2, etc. i.e. B1 is 01-Jan-2019 and B2 is New Year's Day (which all sites observe). C1 is 21-Jan-2019, and C2 is Martin Luther King Day (which only a few sites observe). The name of the holiday is obviously irrelevant, so its just a case of whether there's text in a cell or not.

I am trying to total the number of holidays per month on another worksheet. For each site, I think I'd want to look at the month/year across the columns, and then when there's text in the corresponding site row, add to the count. For instance, in the example above, if a site observes both New Year's Day & Martin Luther King Day as its only January holidays, I'd want a 2 displayed in the cell. To help with this, on this other worksheet, I have the same format with sites in column A, and the dates in row 1 (in the format mmm-yyyy).

Have had no success so far, so any help would be greatly appreciated!

Thanks!

 

[Rijnsent]

Hi slam,
you'd need 1 COUNTIFS formula for that.
In your Second worksheet, cell B1, put the first day of the first month (say 1-1-2019), C1 is the first day of the next month (Feb 1st 2019) etc.
A2: =COUNTIFS(DATASHEET!$1:$1,">="&B$1,DATASHEET!$1:$1,"<"&C$1,DATASHEET!2:2,"<>")
Drag that formula to the other cells, that should work. Hope the formula is understandable?
Cheers,
Koen

 

[slam]

Hi Rijnsent,

Only just getting back to this now, but your formula works perfectly. Thank you so much!