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

Gillian Maser

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?

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.

Gillian Maser

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

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

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

Gillian Maser

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

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

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.

Gillian Maser

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

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.