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

Prevent due dates from falling within the same calendar week?
Hi. I am working on a (roughly) annual document review system, in partnership with another team in my organization. They have requested that they receive one document to review per week. But there are more documents to review than there are weeks in the year. So I need to figure out a method for ensuring that I don't send…
How to record date without time
I would like to use automation on a sheet to record the Open and Closed date for a given RAID item. I don't want to record the time, however. Is there a way to exclude a time stamp from the automation? If it is not possible to exclude the time stamp, what formula would I need to use to extract the datae and time into…
count if value is between a date range (reference between 2 sheets)
Hi team i have 2 sheets for material inventory. the first is to record witch equipment is ship out for repair and the other is the total of equipment available. the first is set as follow: equipment type, date out, date return, quantity (negative number) the other one is as follow: each row is for a single date with the…