Count Number of Records in current year
M. David
✭✭✭✭✭
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.
0
Comments
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()))
thinkspi.com
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.
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?
My mistake...
=COUNTIFS({Master Sheet_Project Status}, [Project Status]@row, {Master Sheet_Move-In Date}, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()))
This should work.
thinkspi.com
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.
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")
thinkspi.com
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.
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.
thinkspi.com