CountIfs Function to Determine if Completed on Time

AshleyDeichmueller

Hi,

I am trying to put together a formula sheet from our data in our inventory. Within the inventory, each project is assigned to someone and given a due date, they will manually enter their completion date.

I want the formula to count # of projects someone completes on time. Here is my current formula:

=COUNTIFS({Assigned Contact}, User@row, {Completion Date}, <={Due Date})

Seems like a simple enough formula, but it only gives me a count of 0, even though some users have completion date = Due date.

Any ideas?

Scott Orsey

Hi @AshleyDeichmueller , You're right. That seems pretty simple, but you do have an issue in the formula. For the condition, you cannot use a range (fourth argument). COUNTIFS() will be looking at each {Completion Date} and comparing it to just one due date (not a range). The easiest way to fix this is to create a helper column (let's call it [On Time]) in your source sheet with a flag that compares [Completion Date]@row to [Due Date]@row and flips the flag if completed on time. Then you can use this [On Time] flag in your COUNTIFS on your summary sheet.

Hope this helps.

Be well

Scott Orsey

HI @AshleyDeichmueller , I feel your pain! I am not aware of a way to compare two values from the same row of a different sheet in a formula like you are attempting to do that also involves counting or summing multiple results. If the Due Date were the same for all of the projects, then you could do it, but I bet that's not the case.

I also appreciate limiting columns to avoid going over SS limits. Keep in mind that you can have up to 400 columns and 500,000 cells in a sheet, so there is a lot of room, even for a relatively large inventory with 1000s of rows. If you are looking at 10s of 1000s of rows, you can consider archiving old data into a second sheet. You can always bring the "active" and "archived" data into the same report if needed. If you are truly coming close to the limits, you are also probably experiencing slow processing times when you open, save or add rows to the sheet. Automations can sometimes take a long time to fire, etc. Archiving can help with that too... and you can create automations that will automatically archive rows as needed.

I realize this isn't exactly what you are looking for, but perhaps it will spark some additional solutions. Adding an On Time column really shouldn't throw the whole design into a tailspin. BTW.. another idea: if you already have a project "status" column (especially if it is calculated with an IF() statement), you could add a new value that represents when a project is completed late. Then you could use that value in your COUNTIFS()

I hope this helps. Be well.

Scott Orsey

Hi @AshleyDeichmueller , You're right. That seems pretty simple, but you do have an issue in the formula. For the condition, you cannot use a range (fourth argument). COUNTIFS() will be looking at each {Completion Date} and comparing it to just one due date (not a range). The easiest way to fix this is to create a helper column (let's call it [On Time]) in your source sheet with a flag that compares [Completion Date]@row to [Due Date]@row and flips the flag if completed on time. Then you can use this [On Time] flag in your COUNTIFS on your summary sheet.

Hope this helps.

Be well

AshleyDeichmueller

Thanks @Scott Orsey ! Is there any other workaround by using a formula - any formulas that do look at a range? I only ask because we have a large inventory with many columns and are trying to limit adding more columns.

Scott Orsey

HI @AshleyDeichmueller , I feel your pain! I am not aware of a way to compare two values from the same row of a different sheet in a formula like you are attempting to do that also involves counting or summing multiple results. If the Due Date were the same for all of the projects, then you could do it, but I bet that's not the case.

I also appreciate limiting columns to avoid going over SS limits. Keep in mind that you can have up to 400 columns and 500,000 cells in a sheet, so there is a lot of room, even for a relatively large inventory with 1000s of rows. If you are looking at 10s of 1000s of rows, you can consider archiving old data into a second sheet. You can always bring the "active" and "archived" data into the same report if needed. If you are truly coming close to the limits, you are also probably experiencing slow processing times when you open, save or add rows to the sheet. Automations can sometimes take a long time to fire, etc. Archiving can help with that too... and you can create automations that will automatically archive rows as needed.

I realize this isn't exactly what you are looking for, but perhaps it will spark some additional solutions. Adding an On Time column really shouldn't throw the whole design into a tailspin. BTW.. another idea: if you already have a project "status" column (especially if it is calculated with an IF() statement), you could add a new value that represents when a project is completed late. Then you could use that value in your COUNTIFS()

I hope this helps. Be well.