Problem counting data when linked to another table

[DrumLad]

[TABLE="width: 1213"]
<tbody>[TR]
[TD]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!









[/TD]
[TD][/TD]
[TD="colspan: 2"]












Attendance Record[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Period[/TD]
[TD]YTD[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Full Name[/TD]
[TD]John Smith[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Team[/TD]
[TD]Asset Engineering Team[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Role[/TD]
[TD]Asset Engineer[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]Period 01[/TD]
[TD="colspan: 4"]Period 02[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]06/04/2018[/TD]
[TD]13/04/2018[/TD]
[TD]20/04/2018[/TD]
[TD]27/04/2018[/TD]
[TD]04/05/2018[/TD]
[TD]11/05/2018[/TD]
[TD]18/05/2018[/TD]
[TD]25/05/2018[/TD]
[/TR]
[TR]
[TD]Team[/TD]
[TD]Full Name[/TD]
[TD]Role[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Asset Protection team[/TD]
[TD]John Smith[/TD]
[TD]Asset Engineer[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Finance Team[/TD]
[TD]Fred Jones[/TD]
[TD]Accountant[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Procurement Team[/TD]
[TD]Paul Davies[/TD]
[TD]Buyer[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[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).