Count instances of text where cell matches month/year

slam

Well-known Member
Joined
Sep 16, 2002
Messages
771
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!
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Rijnsent

Well-known Member
Joined
Oct 17, 2005
Messages
1,266
Office Version
365
Platform
Windows
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

Well-known Member
Joined
Sep 16, 2002
Messages
771
Hi Rijnsent,

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

Watch MrExcel Video

Forum statistics

Threads
1,099,145
Messages
5,466,935
Members
406,511
Latest member
markflayd

This Week's Hot Topics

Top