Observation on COUNTA Function Results with TRIMRANGE/DOT Operator

Vimal Jani 0 Reputation points
2025-10-08T06:15:11.5933333+00:00

Error_fxTRIMRANGE

Dear Team,

Recently I come across an issue related to the behaviour of the TRIMRANGE function and the DOT (.) operator in cell range calculations.

Here’s the scenario I observed:

  • Cells A1 to A10 are completely blank.
  • When applying the formula COUNTA(A1:A10), the result is correctly returned as 0.
  • However, when using COUNTA(TRIMRANGE(A1:A10)) or COUNTA(A1:.A10), the result unexpectedly returns 1, even though all cells in the range are blank.

It appears that either the TRIMRANGE function or the DOT operator is interpreting the range as containing at least one non-blank cell, which may indicate a bug or unintended behavior.

Requesting MS team to review this behavior and advise if there is any known explanation or workaround for this outcome

Thank you in advance for your support.

Microsoft 365 and Office | Excel | For business | Windows
0 comments
Answer recommended by moderator
riny 21,520 Reputation points Volunteer Moderator
2025-10-08T08:35:19.24+00:00

That's because when you use TRIMRANGE are the "." operator on an empty range, Excel returns #REF!. Wrap that in COUNTA and you get 1.

Was this answer helpful?

1 person found this answer helpful.

0 additional answers

Sort by: Most helpful
Most helpful Newest Oldest

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.