"Peo Sjoblom" wrote...
First of all, no need for your ctrl + shift & enter formula, yopu might as
well use
=SUMPRODUCT(--(N2:N4000="NAX"),--(M2:M40009),--(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:M40009),
--(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))
|