Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
multi-array sumproduct
Howdee all.
I'm trying something new with sumproduct that I've always been too intimidated to try-- a 10 array sumproduct function. My goal is to tally the totals of 8 different values, with one common value between them. I set my common arrary 1st-- (E6:E266=E139) I then select my subsequent 8 arrays. Each one having a different criteria-- (A6:A266=Y18) (A6:A266=Y19) (A6:A266=Y20) (A6:A266=Y21) (A6:A266=Y22) (A6:A266=Y23) (A6:A266=Y24) (A6:A266=Y25) For each of these, I anticipate no more than two true responses. I then have my final array (b6:b266) This last array has my values that I want summed. Now, as I understand sumproduct, the true response will return a 1, and false, a 0. As my worksheet is ordered, with my initial array, I get 8 true responses. With my second array- I get two trues. 3rd- two trues; 4th, one, and on out to the 8th basic array. For a reason that I'm not clear on, it appears that if the true response is not in the same position as the previous array's true response, it returns a false-- which is 0 (thereby nullifying the entire response to 0). I get this in smaller arrays, as I do a 3 criteria array sumproduct all the time. It was my hope to have a true for array 1, and 2. A true for array 1 and 3, a true for array 1 and 4, etc.... through 1 and 8. However, the true responses are in a different position for each of the secondary 8 arrays. E.g., let's say that 1 and 2 have a true response at position 30. 1 and 3 have it at pos'n 42, 1 and 4 are at pos'n 45, 1 and 5 are at pos'n 53, 1 & 6 at 156, 1 & 7 at 232, 1 & 8 at 245, and 1 & 9 are at 248. It appears that even if array 1 and 2 is true, but array 1 & 3 is false at the identical position of 42-- even though its true at 45, it nullifies the function. As I write this, I'm beginning to think that I'd be better off either nesting my sumproduct (SP) functions, or doing 8 individual SP eq's. Could someone help clarify this for me? Thank you. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
multi-array sumproduct
Steve wrote:
Howdee all. I'm trying something new with sumproduct that I've always been too intimidated to try-- a 10 array sumproduct function. My goal is to tally the totals of 8 different values, with one common value between them. I set my common arrary 1st-- (E6:E266=E139) I then select my subsequent 8 arrays. Each one having a different criteria-- (A6:A266=Y18) (A6:A266=Y19) (A6:A266=Y20) (A6:A266=Y21) (A6:A266=Y22) (A6:A266=Y23) (A6:A266=Y24) (A6:A266=Y25) For each of these, I anticipate no more than two true responses. I then have my final array (b6:b266) This last array has my values that I want summed. Now, as I understand sumproduct, the true response will return a 1, and false, a 0. As my worksheet is ordered, with my initial array, I get 8 true responses. With my second array- I get two trues. 3rd- two trues; 4th, one, and on out to the 8th basic array. For a reason that I'm not clear on, it appears that if the true response is not in the same position as the previous array's true response, it returns a false-- which is 0 (thereby nullifying the entire response to 0). [snipped] Hi Steve, I think you want something like this: =SUMPRODUCT((E6:E266=E139)*((A6:A266=Y18)+(A6:A266 =Y19)+(A6:A266=Y20)+...)*(b6:b266)) You are missing an important part of how arrays work. If you multiply all the arrays, you will most likely end up with zero if some of the conditions are mutually exclusive. I believe this is your problem. In the formula above I placed what I understand to be your mutually exclusive conditions in an OR clause (note the + signs and extra paren grouping). Know that "+" acts like "OR" and "*" acts like "AND" when dealing with logical expressions. If this doesn't make sense, let us know. However, I suggest you set up a very small test of 5 rows and simulate your conditions. I can provide one if needed. Use the formula auditing tool "evaluate formula" to watch how the arrays are evaluated in SUMPRODUCT. This can be instrumental in understanding how all these concepts work. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
multi-array sumproduct
Hi,
Thanks for the response. So, what I want to do is to sum each of the arrays instead of multiplying them. That makes sense. I'll try that in the morning to see how it works. Thank you. "smartin" wrote: Steve wrote: Howdee all. I'm trying something new with sumproduct that I've always been too intimidated to try-- a 10 array sumproduct function. My goal is to tally the totals of 8 different values, with one common value between them. I set my common arrary 1st-- (E6:E266=E139) I then select my subsequent 8 arrays. Each one having a different criteria-- (A6:A266=Y18) (A6:A266=Y19) (A6:A266=Y20) (A6:A266=Y21) (A6:A266=Y22) (A6:A266=Y23) (A6:A266=Y24) (A6:A266=Y25) For each of these, I anticipate no more than two true responses. I then have my final array (b6:b266) This last array has my values that I want summed. Now, as I understand sumproduct, the true response will return a 1, and false, a 0. As my worksheet is ordered, with my initial array, I get 8 true responses. With my second array- I get two trues. 3rd- two trues; 4th, one, and on out to the 8th basic array. For a reason that I'm not clear on, it appears that if the true response is not in the same position as the previous array's true response, it returns a false-- which is 0 (thereby nullifying the entire response to 0). [snipped] Hi Steve, I think you want something like this: =SUMPRODUCT((E6:E266=E139)*((A6:A266=Y18)+(A6:A266 =Y19)+(A6:A266=Y20)+...)*(b6:b266)) You are missing an important part of how arrays work. If you multiply all the arrays, you will most likely end up with zero if some of the conditions are mutually exclusive. I believe this is your problem. In the formula above I placed what I understand to be your mutually exclusive conditions in an OR clause (note the + signs and extra paren grouping). Know that "+" acts like "OR" and "*" acts like "AND" when dealing with logical expressions. If this doesn't make sense, let us know. However, I suggest you set up a very small test of 5 rows and simulate your conditions. I can provide one if needed. Use the formula auditing tool "evaluate formula" to watch how the arrays are evaluated in SUMPRODUCT. This can be instrumental in understanding how all these concepts work. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
multi-array sumproduct
....((A6:A266=Y18)+(A6:A266=Y19)+(A6:A266=Y20)+... )...
Instead of doing that for 8 arrays it'd be better to use: --(ISNUMBER(MATCH(A6:A266,Y18:Y25,0))) -- Biff Microsoft Excel MVP "smartin" wrote in message ... Steve wrote: Howdee all. I'm trying something new with sumproduct that I've always been too intimidated to try-- a 10 array sumproduct function. My goal is to tally the totals of 8 different values, with one common value between them. I set my common arrary 1st-- (E6:E266=E139) I then select my subsequent 8 arrays. Each one having a different criteria-- (A6:A266=Y18) (A6:A266=Y19) (A6:A266=Y20) (A6:A266=Y21) (A6:A266=Y22) (A6:A266=Y23) (A6:A266=Y24) (A6:A266=Y25) For each of these, I anticipate no more than two true responses. I then have my final array (b6:b266) This last array has my values that I want summed. Now, as I understand sumproduct, the true response will return a 1, and false, a 0. As my worksheet is ordered, with my initial array, I get 8 true responses. With my second array- I get two trues. 3rd- two trues; 4th, one, and on out to the 8th basic array. For a reason that I'm not clear on, it appears that if the true response is not in the same position as the previous array's true response, it returns a false-- which is 0 (thereby nullifying the entire response to 0). [snipped] Hi Steve, I think you want something like this: =SUMPRODUCT((E6:E266=E139)*((A6:A266=Y18)+(A6:A266 =Y19)+(A6:A266=Y20)+...)*(b6:b266)) You are missing an important part of how arrays work. If you multiply all the arrays, you will most likely end up with zero if some of the conditions are mutually exclusive. I believe this is your problem. In the formula above I placed what I understand to be your mutually exclusive conditions in an OR clause (note the + signs and extra paren grouping). Know that "+" acts like "OR" and "*" acts like "AND" when dealing with logical expressions. If this doesn't make sense, let us know. However, I suggest you set up a very small test of 5 rows and simulate your conditions. I can provide one if needed. Use the formula auditing tool "evaluate formula" to watch how the arrays are evaluated in SUMPRODUCT. This can be instrumental in understanding how all these concepts work. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
multi-array sumproduct
Ok, back in the office this morning.... It works..... thank you.
So, + works as an OR operator, and * works as an AND operator with this function. Interesting. This is definitely something I'll be keeping handy for future use. There have been many times when I'd wanted to do something like this but thought I'd be headed for trouble, and wasn't sure which direction I SHOULD go with it. So, thank you very much. Have a great day. "smartin" wrote: Steve wrote: Howdee all. I'm trying something new with sumproduct that I've always been too intimidated to try-- a 10 array sumproduct function. My goal is to tally the totals of 8 different values, with one common value between them. I set my common arrary 1st-- (E6:E266=E139) I then select my subsequent 8 arrays. Each one having a different criteria-- (A6:A266=Y18) (A6:A266=Y19) (A6:A266=Y20) (A6:A266=Y21) (A6:A266=Y22) (A6:A266=Y23) (A6:A266=Y24) (A6:A266=Y25) For each of these, I anticipate no more than two true responses. I then have my final array (b6:b266) This last array has my values that I want summed. Now, as I understand sumproduct, the true response will return a 1, and false, a 0. As my worksheet is ordered, with my initial array, I get 8 true responses. With my second array- I get two trues. 3rd- two trues; 4th, one, and on out to the 8th basic array. For a reason that I'm not clear on, it appears that if the true response is not in the same position as the previous array's true response, it returns a false-- which is 0 (thereby nullifying the entire response to 0). [snipped] Hi Steve, I think you want something like this: =SUMPRODUCT((E6:E266=E139)*((A6:A266=Y18)+(A6:A266 =Y19)+(A6:A266=Y20)+...)*(b6:b266)) You are missing an important part of how arrays work. If you multiply all the arrays, you will most likely end up with zero if some of the conditions are mutually exclusive. I believe this is your problem. In the formula above I placed what I understand to be your mutually exclusive conditions in an OR clause (note the + signs and extra paren grouping). Know that "+" acts like "OR" and "*" acts like "AND" when dealing with logical expressions. If this doesn't make sense, let us know. However, I suggest you set up a very small test of 5 rows and simulate your conditions. I can provide one if needed. Use the formula auditing tool "evaluate formula" to watch how the arrays are evaluated in SUMPRODUCT. This can be instrumental in understanding how all these concepts work. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
multi-array sumproduct
Hi Biff,
Thank you for the response. I wasn't trying to determine IF there was an instance of the elements, rather I actually needed a tally of the values that matched the specific criteria required-- hence the 8 arrays in the middle. I did try yours, and it came back as a false-- which after having found my original 10 array function went to zero, makes sense. The values of each true aren't in the same position within each array. As I've considered that further, the reason my original use-- in times past-- of Sumproduct worked is that the true responses are true for the same position within each array. (too bad I didn't grasp that when I was taking linear algebra... it would've helped a lot....) I had never thought of that this far out before yesterday's use. So, while yours didn't actually "solve" my issue, it did allow me to recognize a little more of the logic involved. Thanks again for your help. Best. "T. Valko" wrote: ....((A6:A266=Y18)+(A6:A266=Y19)+(A6:A266=Y20)+... )... Instead of doing that for 8 arrays it'd be better to use: --(ISNUMBER(MATCH(A6:A266,Y18:Y25,0))) -- Biff Microsoft Excel MVP "smartin" wrote in message ... Steve wrote: Howdee all. I'm trying something new with sumproduct that I've always been too intimidated to try-- a 10 array sumproduct function. My goal is to tally the totals of 8 different values, with one common value between them. I set my common arrary 1st-- (E6:E266=E139) I then select my subsequent 8 arrays. Each one having a different criteria-- (A6:A266=Y18) (A6:A266=Y19) (A6:A266=Y20) (A6:A266=Y21) (A6:A266=Y22) (A6:A266=Y23) (A6:A266=Y24) (A6:A266=Y25) For each of these, I anticipate no more than two true responses. I then have my final array (b6:b266) This last array has my values that I want summed. Now, as I understand sumproduct, the true response will return a 1, and false, a 0. As my worksheet is ordered, with my initial array, I get 8 true responses. With my second array- I get two trues. 3rd- two trues; 4th, one, and on out to the 8th basic array. For a reason that I'm not clear on, it appears that if the true response is not in the same position as the previous array's true response, it returns a false-- which is 0 (thereby nullifying the entire response to 0). [snipped] Hi Steve, I think you want something like this: =SUMPRODUCT((E6:E266=E139)*((A6:A266=Y18)+(A6:A266 =Y19)+(A6:A266=Y20)+...)*(b6:b266)) You are missing an important part of how arrays work. If you multiply all the arrays, you will most likely end up with zero if some of the conditions are mutually exclusive. I believe this is your problem. In the formula above I placed what I understand to be your mutually exclusive conditions in an OR clause (note the + signs and extra paren grouping). Know that "+" acts like "OR" and "*" acts like "AND" when dealing with logical expressions. If this doesn't make sense, let us know. However, I suggest you set up a very small test of 5 rows and simulate your conditions. I can provide one if needed. Use the formula auditing tool "evaluate formula" to watch how the arrays are evaluated in SUMPRODUCT. This can be instrumental in understanding how all these concepts work. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
multi-array sumproduct
Don't know why it didn't work for you.
Consider this simple example: ...........A..........B..........C 1......Yes.........x..........1 2......Yes.........a..........1 3......No..........y..........1 4......No..........b..........1 5......Yes.........z..........1 Sum C1:C5 where A1:A5 = Yes and B1:B5 = x or y or z. Criteria: E1 = Yes F1 = x F2 = y F3 = z Both of these formulas do just that: =SUMPRODUCT((A1:A5=E1)*((B1:B5=F1)+(B1:B5=F2)+(B1: B5=F3))*(C1:C5)) =SUMPRODUCT(--(A1:A5=E1),--(ISNUMBER(MATCH(B1:B5,F1:F3,0))),C1:C5) The ISNUMBER(MATCH(...)) version is the better choice. Especially if you wanted to test for 8 "or" conditions in B1:B5 (as your original post described). -- Biff Microsoft Excel MVP "Steve" wrote in message ... Hi Biff, Thank you for the response. I wasn't trying to determine IF there was an instance of the elements, rather I actually needed a tally of the values that matched the specific criteria required-- hence the 8 arrays in the middle. I did try yours, and it came back as a false-- which after having found my original 10 array function went to zero, makes sense. The values of each true aren't in the same position within each array. As I've considered that further, the reason my original use-- in times past-- of Sumproduct worked is that the true responses are true for the same position within each array. (too bad I didn't grasp that when I was taking linear algebra... it would've helped a lot....) I had never thought of that this far out before yesterday's use. So, while yours didn't actually "solve" my issue, it did allow me to recognize a little more of the logic involved. Thanks again for your help. Best. "T. Valko" wrote: ....((A6:A266=Y18)+(A6:A266=Y19)+(A6:A266=Y20)+... )... Instead of doing that for 8 arrays it'd be better to use: --(ISNUMBER(MATCH(A6:A266,Y18:Y25,0))) -- Biff Microsoft Excel MVP "smartin" wrote in message ... Steve wrote: Howdee all. I'm trying something new with sumproduct that I've always been too intimidated to try-- a 10 array sumproduct function. My goal is to tally the totals of 8 different values, with one common value between them. I set my common arrary 1st-- (E6:E266=E139) I then select my subsequent 8 arrays. Each one having a different criteria-- (A6:A266=Y18) (A6:A266=Y19) (A6:A266=Y20) (A6:A266=Y21) (A6:A266=Y22) (A6:A266=Y23) (A6:A266=Y24) (A6:A266=Y25) For each of these, I anticipate no more than two true responses. I then have my final array (b6:b266) This last array has my values that I want summed. Now, as I understand sumproduct, the true response will return a 1, and false, a 0. As my worksheet is ordered, with my initial array, I get 8 true responses. With my second array- I get two trues. 3rd- two trues; 4th, one, and on out to the 8th basic array. For a reason that I'm not clear on, it appears that if the true response is not in the same position as the previous array's true response, it returns a false-- which is 0 (thereby nullifying the entire response to 0). [snipped] Hi Steve, I think you want something like this: =SUMPRODUCT((E6:E266=E139)*((A6:A266=Y18)+(A6:A266 =Y19)+(A6:A266=Y20)+...)*(b6:b266)) You are missing an important part of how arrays work. If you multiply all the arrays, you will most likely end up with zero if some of the conditions are mutually exclusive. I believe this is your problem. In the formula above I placed what I understand to be your mutually exclusive conditions in an OR clause (note the + signs and extra paren grouping). Know that "+" acts like "OR" and "*" acts like "AND" when dealing with logical expressions. If this doesn't make sense, let us know. However, I suggest you set up a very small test of 5 rows and simulate your conditions. I can provide one if needed. Use the formula auditing tool "evaluate formula" to watch how the arrays are evaluated in SUMPRODUCT. This can be instrumental in understanding how all these concepts work. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
multi-array sumproduct
Excel 2007 Table
With Structured References Full example: http://www.mediafire.com/file/oroorfnmtjz/07_29_09.xlsx |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
multi-array sumproduct
You're welcome. Don't overlook Biff's improvement though. Using
ISNUMBER/MATCH is much easier to read, maintain, and will perform better than stringing out 8 "OR"s. Regarding OR and AND, the + and * operators act in this way in any logical expression--this is not specific to SUMPRODUCT or any other specific function. For example, =(1+1=1) + (1+1=2) yields 1 because it evaluates like TRUE OR FALSE TRUE =(1+1=1) * (1+1=2) yields 0 because TRUE AND FALSE FALSE Steve wrote: Ok, back in the office this morning.... It works..... thank you. So, + works as an OR operator, and * works as an AND operator with this function. Interesting. This is definitely something I'll be keeping handy for future use. There have been many times when I'd wanted to do something like this but thought I'd be headed for trouble, and wasn't sure which direction I SHOULD go with it. So, thank you very much. Have a great day. "smartin" wrote: Steve wrote: Howdee all. I'm trying something new with sumproduct that I've always been too intimidated to try-- a 10 array sumproduct function. My goal is to tally the totals of 8 different values, with one common value between them. I set my common arrary 1st-- (E6:E266=E139) I then select my subsequent 8 arrays. Each one having a different criteria-- (A6:A266=Y18) (A6:A266=Y19) (A6:A266=Y20) (A6:A266=Y21) (A6:A266=Y22) (A6:A266=Y23) (A6:A266=Y24) (A6:A266=Y25) For each of these, I anticipate no more than two true responses. I then have my final array (b6:b266) This last array has my values that I want summed. Now, as I understand sumproduct, the true response will return a 1, and false, a 0. As my worksheet is ordered, with my initial array, I get 8 true responses. With my second array- I get two trues. 3rd- two trues; 4th, one, and on out to the 8th basic array. For a reason that I'm not clear on, it appears that if the true response is not in the same position as the previous array's true response, it returns a false-- which is 0 (thereby nullifying the entire response to 0). [snipped] Hi Steve, I think you want something like this: =SUMPRODUCT((E6:E266=E139)*((A6:A266=Y18)+(A6:A266 =Y19)+(A6:A266=Y20)+...)*(b6:b266)) You are missing an important part of how arrays work. If you multiply all the arrays, you will most likely end up with zero if some of the conditions are mutually exclusive. I believe this is your problem. In the formula above I placed what I understand to be your mutually exclusive conditions in an OR clause (note the + signs and extra paren grouping). Know that "+" acts like "OR" and "*" acts like "AND" when dealing with logical expressions. If this doesn't make sense, let us know. However, I suggest you set up a very small test of 5 rows and simulate your conditions. I can provide one if needed. Use the formula auditing tool "evaluate formula" to watch how the arrays are evaluated in SUMPRODUCT. This can be instrumental in understanding how all these concepts work. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multi column sumproduct | Excel Worksheet Functions | |||
Multi add, in array of data | Excel Worksheet Functions | |||
Multi-Cell Array Formula | Excel Worksheet Functions | |||
a multi-rounded sumproduct | Excel Worksheet Functions | |||
match in multi-column and multi-row array | Excel Discussion (Misc queries) |