summarise one sheet and have results sent to a second worksheet

[CSM24]

Hi,
Could you please offer some advice on following?
Our current accounts package sage 50, cannot calculate any on time delivery performance natively so I have to out reports to excel I have two worksheets in the same work book for monitoring supplier performance,


Sheet 1 “Supplier Register” pulls data down at account level. And incorporates account data to use as a supplier risk database.
Sheet2 “Sup OTD” pulls itemised delivery data for each supplier/order. Delivery performance is calculated using the difference between delivery due date, and date received in the either Early, On Time or Late.

I am looking to


Summarize the on time delivery performance per supplier from sheet 2 “Sup OTD”
Pull or push the summarised on time delivery performance through to Sheet 1 “Supplier Register” for the relevant supplier.


Sample work book link


https://drive.google.com/open?id=1CB...nzcY_pjzImnW_v


Notes.
If it makes things easier Early and On Time can be changed to be on time.


Best Regards


Mark

 

[sijpie]

Your link does not lead to a workbook.

 

[CSM24]

Apologies I will try again

https://drive.google.com/open?id=1CB_rVPekFRxRYqsAZonzcY_pjzImnW_v

 

[sijpie]

In the sheet SUP OTD I have added a column where if there is late delivery I put -1, early delivery +1.

In the sheet Supplier Register, in the column AB (Condition_5(OTD)) I have put a formula to calculate the % late deliveries as a KPI. Maybe you want it differently, then you need to adjust the formula.
The formula is:

=SUMPRODUCT(('SUP OTD'!N2:N31<0)*('SUP OTD'!A2:A31=A4))/COUNTIF('SUP OTD'!A2:A31,A4)

Make sure that the company names in both sheets are identical (in your example sheet one of the tables had an extra space in the names).

The formula works as follows:
The Sumproduct part counts the number of cells in column N where the value is less than 0, and the value in column A is the same as the company name in A4.
This then is divided by the total number of deliveries by that company, giving you a % late deliveries.


File can be found here:
https://drive.google.com/file/d/1UU60EbRD9eMBay_Y1WQZYilihtTKFsaW/view?usp=sharing