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

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?

Find more posts tagged with

Formulas

Project Management

Comments

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.

Sign in to join the conversation!

Quick Links

Trending discussions

Getting error message in approval email
Hey, Is anyone else facing this issue in the approval notifications sent through smartsheet
Add Package Count Based on Customer
I have a sheet with a list of customers in one column, and then the following columns are City, Monday, Tuesday, Wednesday, Thursday, Friday. I need help with a formula that I can put in my sheet summary so that if the customer column says Staples (It can say this in multiple rows) that it will tell me the total package…
Automation not triggering all of the time
Hi Smartsheet community, I've always tried to answer questions but this is my first time posting. So I have formula =IF([Project Status]@row = "Complete", 1, 0) set up in the "Complete Check - Hide column" to check the box when the project status is marked complete. The project status column is tied to several other sheets…