0
This is one of the strangest behaviours I have ever seen for Excel...
In cell A3 I have the formula =COUNTIFS(A$101:A$109,"=ERR!"), cells A101 through A109 contain {,"-",,"-","-",,"ERR!",}. This produces a result of 1, as expected.
When I change A$101 to A$100 in the formula, creating =COUNTIFS(A$100:A$120,"=ERR!"), the resulting value is now 0!!
Cell A100 continas "ok", and none of the other cells have changed.
All the non-empty values in these cells are produced by a formula; the empty cells simply have nothing in them. All the cells in question are formatted as General. I get the same behaviour from COUNTIFS() as I do from COUNTIF(). I am using MS Office 365 ProPlus version 1708.
Can anyone explain this bizarre behaviour?
user2540850
Posted 2018-02-01T22:47:42.353
Reputation: 1
I have tried changing the format of the criteria to "ERR!", "=ERR", and "ERR". All of those produce exactly the same results as "=ERR!" for the two different ranges. – user2540850 – 2018-02-01T22:49:56.520
I have tried to reproduce this behavior and I was unable to. The only suggestions I can offer are to click in the formula bar so Excel will outline the range it is counting, and you can check that it is what you think it should be. The list of data values you give has only 8 items, not 9, but I doubt that is the problem. If the highlighted range contains the "ERR!" value, the count should be at least 1. Good luck. – Bandersnatch – 2018-02-02T22:25:15.430