Count totals of dates per location

[kieranm105]

Hi
I have a Spreadsheet with two tabs (Service Visits) & (Work in Progress).

On the (Service Visits) tab I have a table that shows all the locations (col Q2:Q88) and then the dates across the top (R1:AC1) Jan-21, Feb-21 etc.

On the (Work in Progress) tab I have a table that has the locations in col A2:A990 (there are multiple instances of the same location), along the top I have Year 1, Year 1.5, Year 2 etc (up to Year 5.5). In these cells there is a date where the service takes place (e.g. Jan-22, Mar-21).

What I want is the (Service Visits) table to count the instances of the services for each location for each date, (e.g. GOSH Jan-21 = 10, Jan-22 = 5 etc).

I have tables on the same tab that count the total instances of the dates, but I need to know how many are for which location.

I'm not sure what formula to achieve this, any help would be appreciated.

Thanks
Kieran

[ChemistB]

In R2 copied across and down

=SUMPRODUCT((TEXT(WIP_Table[[Year 1]:[Year 5.5]],"mmm-yy")=Table9[[#Headers],[Jan-21]])*(WIP_Table[Hospital]=[@Location]))

In "Work in Progress" you have data that extends beyond the table (i.e. Row 526) This data is not being summed.

[kieranm105]

Thanks for that ChemistB, much appreciated