Forum Discussion

kittenmeants's avatar
kittenmeants
Brass Contributor
Feb 02, 2023

Service Request Tracking when data is indefinite

I am trying to track service requests per month. 

 

This is the information I am recording:

RequestDue DateActual Month DeliveredActual Date Delivered
11/10/23Jan1/10/23
21/12/23Jan1/10/23

 

This is the data that I need to track:

MonthTotal number of requests per monthNumber of requests completed in the agreed timeframevariance between the due date and date delivered
Jan   

 

I am using COUNTIF for the total number of requests per month in the first column and that is working fine..

 

My question is how would I find the 'number of requests completed in the agreed timeframe' and the 'variance'?

 

I was using C2-B2 to find the number of requests completed, but how do I calculate that as an indefinite number?

excel
Formulas and Functions

1 Reply

  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Feb 02, 2023

    kittenmeants 

    In cell C14 is this formula:

    =SUMPRODUCT(($C$2:$C$10=A14)*($D$2:$D$10<=$B$2:$B$10))

    In cell D14 is this formula for the average days between due date and delivery date:

    =AVERAGE(IF($C$2:$C$10=A14,$B$2:$B$10-$D$2:$D$10))

    Enter this formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.