Possibly COUNTIF? Maybe something else

[alex8954]

I have a table with loads of columns. they all have a unique ID (A). In one column (K) it says a date that something was meant to be completed, in the next column (L) it says when it was actually completed. I am trying to do an analysis and show how many of the Projects (A) where delivered on time.

 

[Peter_SSs]

Welcome to the MrExcel board!

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in Make sure any sensitive data is removed or disguised.

Note that there is also a “Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Candyman8019]

You could insert a column with a formula such as: =DAYS(L2,K2)
copy the formula down through each applicable row.
Then wherever you want your analysis, do a simple countif to show how many projects went over schedule: =COUNTIF(N2:N11,">0")

 

[AhoyNC]

Maybe something like this:

Book2
	A	B	C							J	K	L
1	ID										Date	Comp.
2	145										9/30/2020	10/12/2020
3	147										10/20/2020	10/20/2020
4	200										11/12/2020	11/9/2020
5	202										10/14/2020	10/15/2020
6	206										12/2/2020	12/2/2020
7	135										11/22/2020	11/22/2020
8
9		On Time
10		3
11
12		On time / Early
13		4
Sheet1

Cell Formulas
Range	Formula
B10	B10	=ROWS(FILTER($A$2:$A$7,$L$2:$L$7=$K$2:$K$7,""))
B13	B13	=ROWS(FILTER($A$2:$A$7,$L$2:$L$7<=$K$2:$K$7,""))

 

[Peter_SSs]

@AhoyNC
Those formulas will incorrectly return 1 if there are no rows on time or early.

Alternative suggestion that addresses the above issue:
If it is possible that the Comp date column can be empty then the On time/Early formula would need to be the row 14 formula, not row 13.

20 12 22.xlsm
	A	B	C							J	K	L
1	ID										Date	Comp.
2	145										30/09/2020	12/10/2020
3	147										20/10/2020	20/10/2020
4	200										12/11/2020	9/11/2020
5	202										14/10/2020	15/10/2020
6	206										2/12/2020	2/12/2020
7	135										22/11/2020	22/11/2020
8
9		On Time
10		3
11
12		On time / Early
13		4
14		4
On Time

Cell Formulas
Range	Formula
B10	B10	=COUNT(FILTER(K2:K7,L2:L7=K2:K7,""))
B13	B13	=COUNT(FILTER(K2:K7,L2:L7<=K2:K7,""))
B14	B14	=COUNT(FILTER(K2:K7,(L2:L7<=K2:K7)*(L2:L7<>""),""))

 

[AhoyNC]

@Peter_SSs - Thanks good catch.