View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ronbo Ronbo is offline
external usenet poster
 
Posts: 162
Default SUMPRODUCT formula help

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:A1000M2),--(A1:A1000<M3))*((SUMPRODUCT(--(B1:B1000N2),--(B1:B1000<N3)))))
but I get 8, the count of A:A or 0. I am stuck... any help would be
appreciated.

Thanks,

Ronbo