Formula works although evaluation shows #value

[JeteMc]

This is a curiosity as opposed to a problem. Researching the web for the solution to a way to count values of cells in different sheets I found a formula very similar (I did have to modify slightly) to:

Please Login or Register  to view this content.

The formula gives the expected results, however in the course of running a formula evaluation I found that at a certain point it triggers #value(s). I have two questions: 1) why does the formula trigger the #value(s) as the steps leading up seem fairly routine? 2) how does the formula overcome the #value(s) and give the correct answer?

I have included the file so that you can see the behavior described above. It is possibly helpful to know that the function of the formula is to count the number of times that a cell contains the value of 0, 1 or 2 for a specific date in the three named sheets.
Counting values in multiple tabs.xlsx

Hope that everyone will have a good day.

[:) Sixthsense :)]

I don't see any #VALUE in your workbook

[FDibbins]

Neither do I

[samba_ravi]

Evaluate.JPG
I thing this is what OP is asking for

[:) Sixthsense :)]

Because Evaluate cant able to show the Range Object inside the evaluation process when the passed Text String (Range String) is converted as real range by INDIRECT() function.

[JeteMc]

Thanks for looking, replying, and giving me an answer about the Range Object (I'll do a little checking on that subject).

[FDibbins]

Good luck, and thanks for the feedback