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

Gillian Maser
Gillian Maser ✭✭
edited 12/09/19 in Formulas and Functions

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 B), 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?

Comments

  • L_123
    L_123 ✭✭✭✭✭✭

    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.

  • 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. 

    Look forward to your suggestions!

  • L_123
    L_123 ✭✭✭✭✭✭

    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

  • L_123
    L_123 ✭✭✭✭✭✭

    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))

  • Thank you for your help!

    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? 

  • L_123
    L_123 ✭✭✭✭✭✭

    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

  • L_123
    L_123 ✭✭✭✭✭✭

    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.

  • 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!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    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!