Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Nesting a sumproduct formula within a sumif formula. | Excel Discussion (Misc queries) | |||
SUMPRODUCT Formula | Excel Worksheet Functions | |||
sumproduct formula | Excel Worksheet Functions | |||
SumProduct Formula Help | Excel Worksheet Functions | |||
sumproduct formula | Excel Worksheet Functions |