#NO MATCH result but not for identical formulas

Matthew 5

I have two formulas that are fairly identical but one is producing a #NO MATCH error.

=COUNTIFS(Shop:Shop,Shop@row,[Review Start Date]:[Review Start Date], YEAR(@cell)= YEAR([Review Start Date]@row))

This works just fine and returns the proper counts. The Shop column is a number/text field - and the Review Start Date is a date field and returns the number of shops with reviews conducted during the year the review is conducted.

=COUNTIFS(District:District, District@row, [Review Start Date]:[Review Start Date], YEAR(@cell) = YEAR([Review Start Date]@row))

The District column is a number/text field and the Review Start Date is the same field from the previous formula - all on the same sheet.

This returns the #NO MATCH error - I have looked through the data, they are populated

The only other difference between the Shop field and District field is that the District Field is a calculated field. I am using the same formula with appropriate column changes for calculating the same thing for Region and Division counts and seeing the same issue….

Paul Newcome

Does that error exist in even one single cell within any of the referenced ranges?

Paul Newcome

Does that error exist in even one single cell within any of the referenced ranges?

Matthew 5

@Paul Newcome

Ok, it's Friday…I knew that….

So something I need to work into my routines is setting up a filter for errors by default. This was a case of a single cell that was an issue in about 3 years of data…hiding…just for me.

Set up error handling on the offending cell and everything is back to normal(ish)…

I may throw this into the future requests, but it would be nice to be able to click on the error and have it take me to the cell that first broke the formula.