Count Number of Records in current year

Hello. I am trying to use a COUNTIFS formula to count the number of records in a second sheet that meet two criteria; the Project Status of the record matches the project status of the row where the formula is, and the year of the Move-In Date of the record is the same as the current year. I have the formula in the pic below. I hobbled this together based off of several old community discussions. If I remember correctly, it was working, but has now ceased working correctly.

Master Sheet Project Status is where the project status is I am trying to match with the one on the left.
Master Sheet_Move-In Date is the column I want to be evaluated for the matching year.

The "YEAR(@cell)" to the end is what I am not sure of and the place where I believe the error is. Thanks for looking at this. All help is appreciated.

Operational Count Pic.JPG

Find more posts tagged with

Construction

Formulas

Project Management

Comments

Paul Newcome

What error are you getting?

The syntax is correct, and if it was working before but isn't any longer, I venture to guess that there are probably blanks or non-date values in the date range now. Try the addition of an IFERROR.

=COUNTIFS({Master Sheet_Project Status}, [Project Status]@row, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()))

Mike Wilday

I've also seen cross sheet references break. You might need to click into the formula and ensure your cross-sheet references are still accurate.

M. David

Paul:

I am getting an Invalid Data Type error. I tried the IFERROR formula you posted, but not it is missing one of the arguments. I think you deleted out the Move In Date range for the formula to compare with.

Mike:

I checked the cross sheet references and they look good. I re-created the formula on a whole different row, and no dice. Looking at the definition of the error message, I wonder if it is because there are some cells that are blank. In the two columns of the reference sheet, the first 20 rows have data, and then there is a huge gap and then row 31 has data again. Could this be causing the issue?

Paul Newcome

My mistake...

=COUNTIFS({Master Sheet_Project Status}, [Project Status]@row, {Master Sheet_Move-In Date}, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()))

This should work.

M. David

Paul:

That works to overcome the error, so we do know that is what was causing the error. There is still one problem though. This shouldn't be responding with zero projects Operational. There has to be something wrong with the YEAR(TODAY() part of it. I just tried another approach where I inserted a column and ran an IF statement to see if I could get it to work there and it didn't.

=IF([Move-In Date]1 = YEAR(TODAY()), "Yes", "No")

It put "No" when the date I was referencing was 4/30/19. Is there another way of doing this? All I really want is to look at a date and determine if the year of that date is the same as the year of "today". Thanks for your help.

Paul Newcome

Your IF test did not work because you need to compare the year from the move in date to the year of today.

Try this on your IF test.

=IF(YEAR([Move-In Date]1) = YEAR(TODAY()), "Yes", "No")

M. David

I figured out what the issue is. The format of the Move-In Date column was text/number rather than date. Once I changed this, it works just fine. I will keep the formula you provided with the error check just to ensure it keeps working. Thanks for the help with this one.

Paul Newcome

The column type was going to be my next question.

The error check will also ensure that an error is not thrown if there are any blank or non-date entries within the range.

Sign in to join the conversation!

Quick Links

Trending discussions

Is there a way to create a 3-digit padding for numbers
Hi all, and thanks in advance! I wanted to know if there was any way to create "padding" in Smartsheet. Basically I have account numbers, and a handful are still 2-digit, so they appear as TDL-17, TDL-67, etc. Most of the accounts are 3-digit numbers like TDL-180. I want something that will recognize whether it is 2 or 3…
Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?