Forum Discussion
Solved
Excel COUNTIF function not counting correctly
I've been using a sheet for a long time. Suddenly today, a simple COUNTIF function doesn't work any more. The formula is as simple as they get: =COUNTIF(AC23:SZ23,"L"). There are numerous 'L's in the...
- There might be L’s with leading and trailing spaces. Try this:
=SUMPRODUCT(—(TRIM(AC23:SZ23)=“L”))
There might be L’s with leading and trailing spaces. Try this:
=SUMPRODUCT(—(TRIM(AC23:SZ23)=“L”))
=SUMPRODUCT(—(TRIM(AC23:SZ23)=“L”))
This formula works, thanks.
I checked, and there were training spaces behind the 'L's. Upon investigation it turns out that my staff member typed in 'leave' and then corrected it afterwards. Excel let the word 'leave' reappear when he tried typing in 'L', so he made a space to get the 'L'. And that 'incapacitated' my formula.
- You’re welcome!
