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 |
SUMPRODUCT formula help
"Ronbo" wrote:
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" wrote: 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 |
SUMPRODUCT formula help
PERFECT! Thanks a lot for your time, expertise and response.
Regards, Ronbo "Joe User" wrote: "Ronbo" wrote: 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" wrote: 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 |
All times are GMT +1. The time now is 09:53 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com