SOLVED

I need help with a formula

Copper Contributor

I am working on a Monthly Cycle Time Report for vehicles I have in service. I need a formula to show how many are completed once I input the RO close time. 

 

EXAMPLE: 

 
STOCKRODATE OPENTIME OPENDATE CLOSETIME CLOSETOTALTYPE  TYPETOTALTOTAL DONE
A1126808/24/2010:148/27/202012:003.07CPO  CPO6 
A2127108/24/2010:328/28/202013:004.10CPO  ASIS4 
A3128408/24/2010:538/29/202014:005.13CPO  EXLC1 
A4128808/24/2011:048/30/202015:006.16ASIS  STDT2 
A5143708/25/2010:028/31/202016:006.25EXLC  LEASE RETURN1 
A6144808/25/2010:189/1/202017:007.28ASIS  TOTAL 14 
A7145808/25/2010:349/2/202018:008.31STDT     
A8146208/25/2010:399/3/202019:009.35LEASE RETURN     
A9146308/26/2011:39   CPO     
A10146408/27/2012:39   ASIS     
A11146508/28/2013:39   STDT     
A12146608/29/2014:39   ASIS     
A13146708/30/2015:39   CPO     
A14146808/31/2016:39   CPO     
 
 
 
 
 
3 Replies
best response confirmed by cuong (Microsoft)
Solution

@CrimsonTrail 

Few variants. You may create PivotTable. You may use dynamic arrays formulas if your version of Excel supports it. You may use COUNIFS on per-cell basis if you have pre-defined list of types.

image.png

Formulas are in attached file.

I think I will create a pivot table to help stay organized . I will try these formulas out. Thank you @Sergei Baklan 

@CrimsonTrail , you are welcome

1 best response

Accepted Solutions
best response confirmed by cuong (Microsoft)
Solution

@CrimsonTrail 

Few variants. You may create PivotTable. You may use dynamic arrays formulas if your version of Excel supports it. You may use COUNIFS on per-cell basis if you have pre-defined list of types.

image.png

Formulas are in attached file.

View solution in original post