SOLVED

I need help with a formula

%3CLINGO-SUB%20id%3D%22lingo-sub-1636429%22%20slang%3D%22en-US%22%3EI%20need%20help%20with%20a%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1636429%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20working%20on%20a%20Monthly%20Cycle%20Time%20Report%20for%20vehicles%20I%20have%20in%20service.%20I%20need%20a%20formula%20to%20show%20how%20many%20are%20completed%20once%20I%20input%20the%20RO%20close%20time.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEXAMPLE%3A%26nbsp%3B%3C%2FP%3E%3CDIV%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%3CTABLE%20width%3D%221130px%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2295px%22%3ESTOCK%3C%2FTD%3E%3CTD%20width%3D%2296px%22%3ERO%3C%2FTD%3E%3CTD%20width%3D%2296px%22%3EDATE%20OPEN%3C%2FTD%3E%3CTD%20width%3D%2296px%22%3ETIME%20OPEN%3C%2FTD%3E%3CTD%20width%3D%2296px%22%3EDATE%20CLOSE%3C%2FTD%3E%3CTD%20width%3D%2296px%22%3ETIME%20CLOSE%3C%2FTD%3E%3CTD%20width%3D%22113px%22%3ETOTAL%3C%2FTD%3E%3CTD%20width%3D%2296px%22%3ETYPE%3C%2FTD%3E%3CTD%20width%3D%2240px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2240px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22110px%22%3ETYPE%3C%2FTD%3E%3CTD%20width%3D%2258px%22%3ETOTAL%3C%2FTD%3E%3CTD%20width%3D%2297px%22%3ETOTAL%20DONE%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2295px%22%3EA1%3C%2FTD%3E%3CTD%20width%3D%2296px%22%3E1268%3C%2FTD%3E%3CTD%20width%3D%2296px%22%3E08%2F24%2F20%3C%2FTD%3E%3CTD%20width%3D%2296px%22%3E10%3A14%3C%2FTD%3E%3CTD%20width%3D%2296px%22%3E8%2F27%2F2020%3C%2FTD%3E%3CTD%20width%3D%2296px%22%3E12%3A00%3C%2FTD%3E%3CTD%20width%3D%22113px%22%3E3.07%3C%2FTD%3E%3CTD%20width%3D%2296px%22%3ECPO%3C%2FTD%3E%3CTD%20width%3D%2240px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2240px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22110px%22%3ECPO%3C%2FTD%3E%3CTD%20width%3D%2258px%22%3E6%3C%2FTD%3E%3CTD%20width%3D%2297px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2295px%22%3EA2%3C%2FTD%3E%3CTD%20width%3D%2296px%22%3E1271%3C%2FTD%3E%3CTD%20width%3D%2296px%22%3E08%2F24%2F20%3C%2FTD%3E%3CTD%20width%3D%2296px%22%3E10%3A32%3C%2FTD%3E%3CTD%20width%3D%2296px%22%3E8%2F28%2F2020%3C%2FTD%3E%3CTD%20width%3D%2296px%22%3E13%3A00%3C%2FTD%3E%3CTD%20width%3D%22113px%22%3E4.10%3C%2FTD%3E%3CTD%20width%3D%2296px%22%3ECPO%3C%2FTD%3E%3CTD%20width%3D%2240px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2240px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22110px%22%3EASIS%3C%2FTD%3E%3CTD%20width%3D%2258px%22%3E4%3C%2FTD%3E%3CTD%20width%3D%2297px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2295px%22%3EA3%3C%2FTD%3E%3CTD%20width%3D%2296px%22%3E1284%3C%2FTD%3E%3CTD%20width%3D%2296px%22%3E08%2F24%2F20%3C%2FTD%3E%3CTD%20width%3D%2296px%22%3E10%3A53%3C%2FTD%3E%3CTD%20width%3D%2296px%22%3E8%2F29%2F2020%3C%2FTD%3E%3CTD%20width%3D%2296px%22%3E14%3A00%3C%2FTD%3E%3CTD%20width%3D%22113px%22%3E5.13%3C%2FTD%3E%3CTD%20width%3D%2296px%22%3ECPO%3C%2FTD%3E%3CTD%20width%3D%2240px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2240px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22110px%22%3EEXLC%3C%2FTD%3E%3CTD%20width%3D%2258px%22%3E1%3C%2FTD%3E%3CTD%20width%3D%2297px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2295px%22%3EA4%3C%2FTD%3E%3CTD%20width%3D%2296px%22%3E1288%3C%2FTD%3E%3CTD%20width%3D%2296px%22%3E08%2F24%2F20%3C%2FTD%3E%3CTD%20width%3D%2296px%22%3E11%3A04%3C%2FTD%3E%3CTD%20width%3D%2296px%22%3E8%2F30%2F2020%3C%2FTD%3E%3CTD%20width%3D%2296px%22%3E15%3A00%3C%2FTD%3E%3CTD%20width%3D%22113px%22%3E6.16%3C%2FTD%3E%3CTD%20width%3D%2296px%22%3EASIS%3C%2FTD%3E%3CTD%20width%3D%2240px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2240px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22110px%22%3ESTDT%3C%2FTD%3E%3CTD%20width%3D%2258px%22%3E2%3C%2FTD%3E%3CTD%20width%3D%2297px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2295px%22%3EA5%3C%2FTD%3E%3CTD%20width%3D%2296px%22%3E1437%3C%2FTD%3E%3CTD%20width%3D%2296px%22%3E08%2F25%2F20%3C%2FTD%3E%3CTD%20width%3D%2296px%22%3E10%3A02%3C%2FTD%3E%3CTD%20width%3D%2296px%22%3E8%2F31%2F2020%3C%2FTD%3E%3CTD%20width%3D%2296px%22%3E16%3A00%3C%2FTD%3E%3CTD%20width%3D%22113px%22%3E6.25%3C%2FTD%3E%3CTD%20width%3D%2296px%22%3EEXLC%3C%2FTD%3E%3CTD%20width%3D%2240px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2240px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22110px%22%3ELEASE%20RETURN%3C%2FTD%3E%3CTD%20width%3D%2258px%22%3E1%3C%2FTD%3E%3CTD%20width%3D%2297px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2295px%22%3EA6%3C%2FTD%3E%3CTD%20width%3D%2296px%22%3E1448%3C%2FTD%3E%3CTD%20width%3D%2296px%22%3E08%2F25%2F20%3C%2FTD%3E%3CTD%20width%3D%2296px%22%3E10%3A18%3C%2FTD%3E%3CTD%20width%3D%2296px%22%3E9%2F1%2F2020%3C%2FTD%3E%3CTD%20width%3D%2296px%22%3E17%3A00%3C%2FTD%3E%3CTD%20width%3D%22113px%22%3E7.28%3C%2FTD%3E%3CTD%20width%3D%2296px%22%3EASIS%3C%2FTD%3E%3CTD%20width%3D%2240px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2240px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22110px%22%3ETOTAL%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2258px%22%3E14%3C%2FTD%3E%3CTD%20width%3D%2297px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2295px%22%3EA7%3C%2FTD%3E%3CTD%20width%3D%2296px%22%3E1458%3C%2FTD%3E%3CTD%20width%3D%2296px%22%3E08%2F25%2F20%3C%2FTD%3E%3CTD%20width%3D%2296px%22%3E10%3A34%3C%2FTD%3E%3CTD%20width%3D%2296px%22%3E9%2F2%2F2020%3C%2FTD%3E%3CTD%20width%3D%2296px%22%3E18%3A00%3C%2FTD%3E%3CTD%20width%3D%22113px%22%3E8.31%3C%2FTD%3E%3CTD%20width%3D%2296px%22%3ESTDT%3C%2FTD%3E%3CTD%20width%3D%2240px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2240px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22110px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2258px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2297px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2295px%22%3EA8%3C%2FTD%3E%3CTD%20width%3D%2296px%22%3E1462%3C%2FTD%3E%3CTD%20width%3D%2296px%22%3E08%2F25%2F20%3C%2FTD%3E%3CTD%20width%3D%2296px%22%3E10%3A39%3C%2FTD%3E%3CTD%20width%3D%2296px%22%3E9%2F3%2F2020%3C%2FTD%3E%3CTD%20width%3D%2296px%22%3E19%3A00%3C%2FTD%3E%3CTD%20width%3D%22113px%22%3E9.35%3C%2FTD%3E%3CTD%20width%3D%2296px%22%3ELEASE%20RETURN%3C%2FTD%3E%3CTD%20width%3D%2240px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2240px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22110px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2258px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2297px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2295px%22%3EA9%3C%2FTD%3E%3CTD%20width%3D%2296px%22%3E1463%3C%2FTD%3E%3CTD%20width%3D%2296px%22%3E08%2F26%2F20%3C%2FTD%3E%3CTD%20width%3D%2296px%22%3E11%3A39%3C%2FTD%3E%3CTD%20width%3D%2296px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2296px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22113px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2296px%22%3ECPO%3C%2FTD%3E%3CTD%20width%3D%2240px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2240px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22110px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2258px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2297px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2295px%22%3EA10%3C%2FTD%3E%3CTD%20width%3D%2296px%22%3E1464%3C%2FTD%3E%3CTD%20width%3D%2296px%22%3E08%2F27%2F20%3C%2FTD%3E%3CTD%20width%3D%2296px%22%3E12%3A39%3C%2FTD%3E%3CTD%20width%3D%2296px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2296px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22113px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2296px%22%3EASIS%3C%2FTD%3E%3CTD%20width%3D%2240px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2240px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22110px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2258px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2297px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2295px%22%3EA11%3C%2FTD%3E%3CTD%20width%3D%2296px%22%3E1465%3C%2FTD%3E%3CTD%20width%3D%2296px%22%3E08%2F28%2F20%3C%2FTD%3E%3CTD%20width%3D%2296px%22%3E13%3A39%3C%2FTD%3E%3CTD%20width%3D%2296px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2296px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22113px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2296px%22%3ESTDT%3C%2FTD%3E%3CTD%20width%3D%2240px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2240px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22110px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2258px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2297px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2295px%22%3EA12%3C%2FTD%3E%3CTD%20width%3D%2296px%22%3E1466%3C%2FTD%3E%3CTD%20width%3D%2296px%22%3E08%2F29%2F20%3C%2FTD%3E%3CTD%20width%3D%2296px%22%3E14%3A39%3C%2FTD%3E%3CTD%20width%3D%2296px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2296px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22113px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2296px%22%3EASIS%3C%2FTD%3E%3CTD%20width%3D%2240px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2240px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22110px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2258px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2297px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2295px%22%3EA13%3C%2FTD%3E%3CTD%20width%3D%2296px%22%3E1467%3C%2FTD%3E%3CTD%20width%3D%2296px%22%3E08%2F30%2F20%3C%2FTD%3E%3CTD%20width%3D%2296px%22%3E15%3A39%3C%2FTD%3E%3CTD%20width%3D%2296px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2296px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22113px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2296px%22%3ECPO%3C%2FTD%3E%3CTD%20width%3D%2240px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2240px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22110px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2258px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2297px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2295px%22%3EA14%3C%2FTD%3E%3CTD%20width%3D%2296px%22%3E1468%3C%2FTD%3E%3CTD%20width%3D%2296px%22%3E08%2F31%2F20%3C%2FTD%3E%3CTD%20width%3D%2296px%22%3E16%3A39%3C%2FTD%3E%3CTD%20width%3D%2296px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2296px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22113px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2296px%22%3ECPO%3C%2FTD%3E%3CTD%20width%3D%2240px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2240px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22110px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2258px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2297px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CDIV%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1636429%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ENeed%20Help%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1637163%22%20slang%3D%22en-US%22%3ERe%3A%20I%20need%20help%20with%20a%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1637163%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F782491%22%20target%3D%22_blank%22%3E%40CrimsonTrail%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFew%20variants.%20You%20may%20create%20PivotTable.%20You%20may%20use%20dynamic%20arrays%20formulas%20if%20your%20version%20of%20Excel%20supports%20it.%20You%20may%20use%20COUNIFS%20on%20per-cell%20basis%20if%20you%20have%20pre-defined%20list%20of%20types.%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20236px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F216701iB74040F7C840EB88%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EFormulas%20are%20in%20attached%20file.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New 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