Calculating average based on required criteria

[SColyn]

Good day,

I require assistance please.

I have to calculate an average based on the monthly contract data.

We have a Hit rate per customer per month and some of our customer have contracts with us, but the contracts were not implemented the beginning of the year. Some were only implemented August / September.

I have to show the Hit rate average for contract customer and non contract customers but I have to show the months the customer did not have a contract under non contract and visa versa.

I am a blank with how to calculate this and would appreciate guidance.

Below is the Pivot table (and I use to filter on Contracts "Yes / No", but have to include accurate results and cannot do it this way anymore)

Contract	Yes
Sum of Hit Rate		Months	Date
		Jan	Feb	Mar	Apr	May	Jul	Aug	Sep
Market Company	Customer Plant
South Africa	CUSTOMER 1 BOTSWANA	0%	0%	0%	0%	50%	75%	75%	75%
	CUSTOMER 10 SOUTHERN AFRICA BOKSBURG	*	*	*	*	100%	100%	100%	100%
	CUSTOMER 7 LUSAKA	*	0%	0%	0%	50%	75%	50%	60%
South Africa Total		0%	0%	0%	0%	57%	80%	73%	77%

<tbody>
</tbody><colgroup><col><col><col span="8"></colgroup>

Below is the data (but the last column "Contract" is a vlookup from the Contract data sheet and only shows the current status)

Country and Business Area	Customer Plant	Line Code	Asset Code	Serial Number	Total Potential Events	TP Completed Events (Hits)	Date	Contract
Botswana Carton	CUSTOMER 1 BOTSWANA	BWC-CUSTOMER 1 GAB-LN Y	9000030871	20134/81339	1	0	Jan-17	Yes
Botswana Carton	CUSTOMER 1 BOTSWANA	BWC-CUSTOMER 1 GBR-LN G	9000050716	13372/11230	0	0	Jan-17	Yes
Botswana Carton	CUSTOMER 1 BOTSWANA	BWC-CUSTOMER 1 GBR-LN S	9000049891	20179/81785	0	0	Jan-17	Yes
Botswana Carton	CUSTOMER 1 BOTSWANA	BWC-CUSTOMER 1 GBR-LN W	9000030865	20134/81333	0	0	Jan-17	Yes
Botswana Carton	CUSTOMER 1 BOTSWANA	BWC-CUSTOMER 1 GBR-LN G	9000050716	13372/11230	0	0	May-17	Yes
Botswana Carton	CUSTOMER 1 BOTSWANA	BWC-CUSTOMER 1 GBR-LN S	9000049891	20179/81785	1	1	May-17	Yes
Botswana Carton	CUSTOMER 1 BOTSWANA	BWC-CUSTOMER 1 GBR-LN W	9000030865	20134/81333	1	1	May-17	Yes
Botswana Carton	CUSTOMER 1 BOTSWANA	BWC-CUSTOMER 1 GBR-LN Y	9000030871	20134/81339	2	0	May-17	Yes
Botswana Carton	CUSTOMER 1 BOTSWANA	BWC-CUSTOMER 1 GBR-LN G	9000050716	13372/11230	0	0	Aug-17	Yes
Botswana Carton	CUSTOMER 1 BOTSWANA	BWC-CUSTOMER 1 GBR-LN S	9000049891	20179/81785	1	1	Aug-17	Yes
Botswana Carton	CUSTOMER 1 BOTSWANA	BWC-CUSTOMER 1 GBR-LN W	9000030865	20134/81333	1	1	Aug-17	Yes
Botswana Carton	CUSTOMER 1 BOTSWANA	BWC-CUSTOMER 1 GBR-LN Y	9000030871	20134/81339	2	1	Aug-17	Yes
Botswana Carton	CUSTOMER 1 BOTSWANA	BWC-CUSTOMER 1 GBR-LN G	9000050716	13372/11230	0	0	Sep-17	Yes
Botswana Carton	CUSTOMER 1 BOTSWANA	BWC-CUSTOMER 1 GBR-LN S	9000049891	20179/81785	1	1	Sep-17	Yes
Botswana Carton	CUSTOMER 1 BOTSWANA	BWC-CUSTOMER 1 GBR-LN W	9000030865	20134/81333	1	1	Sep-17	Yes
Botswana Carton	CUSTOMER 1 BOTSWANA	BWC-CUSTOMER 1 GBR-LN Y	9000030871	20134/81339	2	1	Sep-17	Yes

<tbody>
</tbody><colgroup><col><col><col><col><col><col><col><col><col></colgroup>

Below is my Contract data sheet

Market Company	Asset Code	Contract	Jan-17	Feb-17	Mar-17	Apr-17	May-17	Jun-17	Jul-17	Aug-17	Sep-17	Oct-17	Nov-17	Dec-17
South Africa	9000049829	No	0	0	0	0	0	0	0	0	0	0	0	0
South Africa	9000049838	No	0	0	0	0	0	0	0	0	0	0	0	0
South Africa	9000052726	No	0	0	0	0	0	0	0	0	0	0	0	0
South Africa	9000049835	No	0	0	0	0	0	0	0	0	0	0	0	0
South Africa	9000079618	No	0	0	0	0	0	0	0	0	0	0	0	0
South Africa	9000079619	No	0	0	0	0	0	0	0	0	0	0	0	0
South Africa	9000085414	No	0	0	0	0	0	0	0	0	0	0	0	0
South Africa	9000079618	No	0	0	0	0	0	0	0	0	0	0	0	0
South Africa	9000079619	No	0	0	0	0	0	0	0	0	0	0	0	0
South Africa	9000085414	No	0	0	0	0	0	0	0	0	0	0	0	0
South Africa	9000030871	Yes	0	0	0	0	0	0	0	1	1	1	1	1
South Africa	9000049891	Yes	0	0	0	0	0	0	0	1	1	1	1	1
South Africa	9000050716	Yes	0	0	0	0	0	0	0	1	1	1	1	1
South Africa	9000030871	Yes	0	0	0	0	0	0	0	1	1	1	1	1
South Africa	9000049891	Yes	0	0	0	0	0	0	0	1	1	1	1	1
South Africa	9000030865	Yes	0	0	0	0	0	0	0	1	1	1	1	1

<tbody>
</tbody><colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup>