Using Sheet Summary to calculate tasks completed on time

Daniel Barber

I am struggling to find the right formula to use in the sheet summary to count the number of tasks completed on time.

For this scenario a task would be considered on time if the 'Finish Date' was on or before the 'Target Finish Date' and the 'Status' = 'Complete'. So essentially a two IF statement spanning three columns with potentially hundreds of rows.

Is this something that the Sheet Summary can handle?

Jesse G.

Hi Danial,
I think I have a formula that should be able to do the trick. What I would recommend is to add a New Helper Column which says:

=IF(AND([Finish Date]@row=<[Target Finish Date]@row,Status@row="Complete"),"Completed on time","Not on Time")

In the sheet summary you can do a countif on "Completed on time" so that whould give:

=COUNTIF([New Helper Column]:[New Helper Column],"Completed on time")

This should count every row with the given criteria. Could you give this a try?