countif array formula

[Rita]

I am using a formula
=COUNT(IF(($N$2:$N$4000="NAX")*($M$2:$M$4000>9)*($M$2:$M$4000<15),$M$2:$M$4000))
which tallies all entries in the range M2:M4000 that are NAX and between
10-14 value. Now I need to change NAX to be a wildcard so that it can count
NAA, NAE, NAX, NAH etc with a value between 10-14. I have tried
=COUNT(IF(($N$2:$N$4000="NA?")*($M$2:$M$4000>9)*($M$2:$M$4000<15),$M$2:$M$4000)) but that doesn't work. Please help.

 

[Frank Kabel]

Hi
better to use sUMPRODUCT. Try (non-array entered):
=SUMPRODUCT(--(LEFT($N$2:$N$4000,2)="NA"),--($M$2:$M$4000>9),--($M$2:$M$4000<>""))

Also see:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

 

[Peo Sjoblom]

First of all, no need for your ctrl + shift & enter formula, yopu might as
well use

=SUMPRODUCT(--(N2:N4000="NAX"),--(M2:M4000>9),--(M2:M4000<15))

instead of the count formula, to get the equivalent of the wild card you
can use

=SUMPRODUCT(--(LEFT(TRIM(N2:N4000),2)="NA"),--(M2:M4000>9),--(M2:M4000<15))

Regards,

Peo Sjoblom

 

[Harlan Grove]

First of all, no need for your ctrl + shift & enter formula, yopu might as
well use

=SUMPRODUCT(--(N2:N4000="NAX"),--(M2:M4000>9),--(M2:M4000<15))

If it's all hardcoded, might as well use

=SUMPRODUCT(--(N2:N4000="NAX"),--((M2:M4000-12)^2<9))

to provide identical functionality, but if the OP really means between 10
and 14 inclusive, better to use

=SUMPRODUCT(--(N2:N4000="NAX"),--((M2:M4000-12)^2<=4))

instead of the count formula, to get the equivalent of the wild card you
can use

=SUMPRODUCT(--(LEFT(TRIM(N2:N4000),2)="NA"),--(M2:M4000>9),
--(M2:M4000<15))

Does the OP want, e.g., NAPALM, NAVY or NAUSEOUS to be matches as well?
Using a wildcard match like "NA?" is pretty clear that col N matches should
have only 3 chars, otherwise "NA*" would make more sense. So safer to use

=SUMPRODUCT(--(LEN(N2:N4000)=3),--(LEFT(N2:N4000,2)="NA"),
--((M2:M4000-12)^2<=4))