SUMPRODUCT formula help

[Ronbo]

I need help creating a SUMPRODUCT formula that will find numbers in A:A that
are in between (or equal to) the criteria in cells M2 (low value) and M3
(high value). Then I need for it to look in B:B for values in between (or
equal to) the criteria in N2 (low value) and N3 (high value). Then I need
the number of times that both occur in the same row. For example; M2/M3 and
N2/N3 =

-84.5175 33.89
-84.44 33.955

Data in A:B =

-84.379978 33.900357
-84.277411 33.900038
-84.467503 33.897839*
-84.467503 33.897839*
-84.248771 33.897616
-84.539292 33.896949
-84.498607 33.896651*
-84.498607 33.896651*
-84.197425 33.896211
-84.285501 33.895625

Thus the formula should return 4(*).

I have been trying something like;
SUMPRODUCT(IF(AND(A1:A1000>M2),--(A1:A1000<M3))*((SUMPRODUCT(--(B1:B1000>N2),--(B1:B1000<N3)))))
but I get 8, the count of A:A or 0. I am stuck... any help would be
appreciated.

Thanks,

Ronbo

 

[Joe User]

I need help creating a SUMPRODUCT formula that will find numbers in A:A that
are in between (or equal to) the criteria in cells M2 (low value) and M3
(high value). Then I need for it to look in B:B for values in between (or
equal to) the criteria in N2 (low value) and N3 (high value). Then I need
the number of times that both occur in the same row.

First of all, I want to commend you on your posting. Well organized; well
stated; with all the necessary elements, right down to a concise and
applicable example. You have no idea what a rarity that is in these forums.
I will bookmark the Google Groups URL for your posting, and point
less-reasonable people to it as an example.

To answer your question, try:

=sumproduct((M2<=A1:A1000)*(A1:A1000<=M3)*(N2<=B1:B1000)*(B1:B1000<=N3))

The multiplication (*) functions as "AND" in this context. You cannot use
AND directly in this context because AND would process the array argument,
not SUMPRODUCT.


----- original message -----

 

[Ronbo]

PERFECT! Thanks a lot for your time, expertise and response.

Regards,
Ronbo