countif - ajit

[Ajit Munj]

In Excel 2000, I have used formula :
=countif(Muster!$M5:$M283,D5)
where Muster is a sheet and range M5:M283 contains newspaper
name like TOI, FE, ECO, LS etc.(abbreviations for newspaper full
name). But this formula is showing wrong result for TOI as 20
whereas when manually checked total TOI are more than 50 in
the said range. Surprisingly, when I delete any TOI string in the
said range from M5:M283, immediately the result shows 19.
Where am I going wrong?

 

[Max]

=countif(Muster!$M5:$M283,D5)

Try instead: =SUMPRODUCT(--(TRIM(Muster!$M$5:$M$283)=D5))

The TRIM() should help remove any extraneous white spaces within the source
range: Muster!$M$5:$M$283, which are probably the culprits fouling up the
correct matching / count