Possibly COUNTIF? Maybe something else

alex8954

New Member
Joined
Dec 16, 2020
Messages
1
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,453
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Dec 2, 2020
Messages
138
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Oct 10, 2011
Messages
4,811
Office Version
  1. 365
Platform
  1. Windows
Maybe something like this:

Book2
ABCJKL
1IDDateComp.
21459/30/202010/12/2020
314710/20/202010/20/2020
420011/12/202011/9/2020
520210/14/202010/15/2020
620612/2/202012/2/2020
713511/22/202011/22/2020
8
9On Time
103
11
12On time / Early
134
Sheet1
Cell Formulas
RangeFormula
B10B10=ROWS(FILTER($A$2:$A$7,$L$2:$L$7=$K$2:$K$7,""))
B13B13=ROWS(FILTER($A$2:$A$7,$L$2:$L$7<=$K$2:$K$7,""))
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,453
Office Version
  1. 365
Platform
  1. Windows
@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
ABCJKL
1IDDateComp.
214530/09/202012/10/2020
314720/10/202020/10/2020
420012/11/20209/11/2020
520214/10/202015/10/2020
62062/12/20202/12/2020
713522/11/202022/11/2020
8
9On Time
103
11
12On time / Early
134
144
On Time
Cell Formulas
RangeFormula
B10B10=COUNT(FILTER(K2:K7,L2:L7=K2:K7,""))
B13B13=COUNT(FILTER(K2:K7,L2:L7<=K2:K7,""))
B14B14=COUNT(FILTER(K2:K7,(L2:L7<=K2:K7)*(L2:L7<>""),""))
 

Forum statistics

Threads
1,136,776
Messages
5,677,666
Members
419,711
Latest member
dacrmcvega0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top