Problem counting data when linked to another table

[DrumLad]

Hi, I am trying to come up with a solution to an excel problem I have. I am looking to devise a formula that counts the number of attendances depending on the name. The main table below is an extract from a larger table with a full list of peoples attendance records. Is there anyway of devising a formula that will lookup the name from the table (based on what name you put at the top and counts the number of attendances both for the period and YTD!		Attendance Record
		Period	YTD
Full Name	John Smith
Team	Asset Engineering Team
Role	Asset Engineer
			Period 01	Period 02
			06/04/2018	13/04/2018	20/04/2018	27/04/2018	04/05/2018	11/05/2018	18/05/2018	25/05/2018
Team	Full Name	Role
Asset Protection team	John Smith	Asset Engineer	1	1		1
Finance Team	Fred Jones	Accountant		1	1	1
Procurement Team	Paul Davies	Buyer	1	1	1	1

<tbody>
</tbody>

 

[Jerry Sullivan]

Hi Drumlad,

It's appears you are working with a PivotTable. Is that correct?

If so, you can use a GETPIVOTDATA formula to reference values from the data fields in the PivotTable. Try that first to get the count for a single date for a specified combination of Full Name, Team and Role.

Once you have that working with dynamic entries at the top of your sheet, you would need to modify those to work for a specified period and YTD. It's possible to do that using an array formula, but the much easier approach would be to add sub-totals for Period and YTD (presumably your Grand Total).