sum, countif giging wrong results?

[zrx1200]

SUM(COUNTIF($D4:$M4,"*"&$AC$1&"*"),COUNTIF($D4:$M4,"*"&$AD$1&"*"),COUNTIF($D4:$M4,"*"&$AE$1&"*"),COUNTIF($D4:$M4,"*"&$AF$1&"*"),COUNTIF($D4:$M4,"*"&$AG$1&"*"),COUNTIF($D4:$M4,"*"&$AH$1&"*"),COUNTIF($D4:$M4,"*"&$AI$1&"*"),COUNTIF($D4:$M4,"*"&$AJ$1&"*"))

I have the above formula copied down 3,000 rows but some are not getting correct answer ?

AC1 through Aj1 contain (E), (N), (S), (W), (SW) (SE), (NW), (NE)

The ones that fail seem to be counting the range D2:M2 which cells have population not necessarily the directions AC1 through AJ1.

I'm I missing something here?

 

[shg]

You could replace that with

=SUM(COUNTIF($D4:$M4, "*" & $AC$1:$AJ$1 & "*"))

I can't make a suggestion about the problem without seeing the data.