Create a Formula that compares Dates in 2 Columns and Counts a Total

Options
edited 12/09/19

Hi all,

I am trying to write a COUNTIFS formula that compares multiple criteria.

I need to Count all items in a column that have a status of "Completed" (column A), IF the items also have an Actual End Date within 2019 (column , and the Actual End Date is less than or equal to the Scheduled End Date (column C).

I cannot add another column to the source sheet. I am creating all formulas in a separate "Formulas sheet". Therefore, I need the formula to be setup so that it just delivers the "answer"....Ex. 4 items in Column A meet the above criteria.

Can anyone help guide me with this?

• ✭✭✭✭✭✭
Options

do you have a unique identifier column inside of your main sheet? Do you care how large your calculation sheet gets? is your primary sheet held to a specific range? If you can answer these questions I have a few solutions for you.

• Options

yes there is a unique identifier column in the source sheet (project number)

There are lots of formulas in the calculations sheet so I'd prefer to keep it clean/not super large if there is another option.

No the primary sheet needs to references the whole column, not a specific range.

• ✭✭✭✭✭✭
Options

The most likely formula that will work depends on you having a unique column in your sheet. By unique I mean that it won't have any blanks, and it won't have any repeat values. If you have one, or can simply add an autonumber column to the main sheet, then the formula below will work for you.

=COUNT(COLLECT(PE:PE, I:I, INDEX(AE:AE, MATCH(@cell, I:I, 0)) <= INDEX(PE:PE, MATCH(@cell, I:I, 0)), Completed:Completed, 1))

PE = Predicted End

AE = Actual End

I = Unique Index

Completed = Completed Checkbox

• ✭✭✭✭✭✭
Options

Forgot the 2019 part

=COUNT(COLLECT(PE:PE, I:I, INDEX(AE:AE, MATCH(@cell, I:I, 0)) <= INDEX(PE:PE, MATCH(@cell, I:I, 0)), Completed:Completed, 1, AE:AE, YEAR(@cell) = 2019))

• Options

I entered the formula but am getting a returned answer of 1. But in looking at the data it should be 4.

Here's the formula I entered:

=COUNT(COLLECT({Global RFID Project Master List Range 3}, {Global RFID Project Master List Range 12}, INDEX({Global RFID Project Master List Range 6}, MATCH(@cell, {Global RFID Project Master List Range 12}, 0)) <= INDEX({Global RFID Project Master List Range 3}, MATCH(@cell, {Global RFID Project Master List Range 12}, 0)), {Global RFID Project Master List Range 1}, @cell = "completed", {Global RFID Project Master List Range 6}, YEAR(@cell = 2019)))

Global RFID Project Master List Range 3 = Predicted End Date column

Global RFID Project Master List Range 12 = Unique ID column

Global RFID Project Master List Range 6 = Actual End Date column

Global RFID Project Master List Range 1 = Status Column

Any ideas where I went wrong?

• ✭✭✭✭✭✭
Options

The thing I see at a glance is the year formula at the end is incorrect. it should be

year(@cell) = 2019

Not

Year(@cell = 2019)

I'll look it over some more as well

• ✭✭✭✭✭✭
Options

I don't see anything else wrong.

When you are troubleshooting a collect formula that is returning an incorrect value, the first step after rereading and checking for basic syntax errors should be to change the calculation formula (in this case count()) to join(. This will show you what the collect is returning, and how it is returning the values, and allow you to troubleshoot exactly where the problem is. I'm guessing the incorrect year syntax brought back an error which the count formula counted as a single return for the collect in this case.

• Options

Yes I think the year formula is the issue.I changed it to read as follows and it returned the correct value!

=COUNT(COLLECT({Global RFID Project Master List Range 3}, {Global RFID Project Master List Range 12}, INDEX({Global RFID Project Master List Range 6}, MATCH(@cell, {Global RFID Project Master List Range 12}, 0)) <= INDEX({Global RFID Project Master List Range 3}, MATCH(@cell, {Global RFID Project Master List Range 12}, 0)), {Global RFID Project Master List Range 1}, @cell = "Completed", {Global RFID Project Master List Range 6}, >DATE(2019, 1, 1)))

Thank you so much for the help!

• ✭✭✭✭✭✭
Options

Changing the main function to a JOIN is a great troubleshooting idea. The only time it gets frustrating is when you have a higher number being returned. You can get quite a long text string to dig through if you aren't careful.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!