Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT on filtered rows
I have a rectangular range of number, with a row header on top of it.
Then I apply a filter. Say that I want the sumproduct of the visible cells in columns B & C A B C 1 1 2 0 2 1 1 3 -1 1 2 3 0 1 2 Then I filter on column A equal to 1 The figure that I need is 1*2+3*(-1)+2*3 = 6 If I use the Sumproduct function I get 10, because the rows starting with 0 are not filtered out. Can I get the result I want with sumproduct? (I think not) Can I use Subtotal instead? without using an auxiliary column neither. Any suggestions? Please, I do not want to use D as an intermediary column for the result of B*C Thank you in advance |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT on filtered rows
=SUMPRODUCT((A2:A6=1)*B2:B6*C2:C6)
The answer is 5, of course, not 6. -- David Biddulph "vsoler" wrote in message ... I have a rectangular range of number, with a row header on top of it. Then I apply a filter. Say that I want the sumproduct of the visible cells in columns B & C A B C 1 1 2 0 2 1 1 3 -1 1 2 3 0 1 2 Then I filter on column A equal to 1 The figure that I need is 1*2+3*(-1)+2*3 = 6 If I use the Sumproduct function I get 10, because the rows starting with 0 are not filtered out. Can I get the result I want with sumproduct? (I think not) Can I use Subtotal instead? without using an auxiliary column neither. Any suggestions? Please, I do not want to use D as an intermediary column for the result of B*C Thank you in advance |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT on filtered rows
On 5 ago, 21:20, "David Biddulph" <groups [at] biddulph.org.uk wrote:
=SUMPRODUCT((A2:A6=1)*B2:B6*C2:C6) The answer is 5, of course, not 6. -- David Biddulph "vsoler" wrote in message ... I have a rectangular range of number, with a row header on top of it. Then I apply a filter. Say that I want the sumproduct of the visible cells in columns B & C A * * B * * C 1 * * 1 * * *2 0 * * 2 * * *1 1 * * 3 * * *-1 1 * * 2 * * *3 0 * * 1 * * *2 Then I filter on column A equal to 1 The figure that I need is *1*2+3*(-1)+2*3 = 6 If I use the Sumproduct function I get 10, because the rows starting with 0 are not filtered out. Can I get the result I want with sumproduct? (I think not) Can I use Subtotal instead? without using an auxiliary column neither. Any suggestions? Please, I do not want to use D as an intermediary column for the result of B*C Thank you in advance Thank you David. However, my actual excel model contains labels in column A, not zeroes and ones (and of course the result should be 5) Is there anything that I can do? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT on filtered rows
Yes, use the labels in your column A criterion.
=SUMPRODUCT((A2:A6="required label value")*B2:B6*C2:C6) or =SUMPRODUCT((A2:A6=D1)*B2:B6*C2:C6) if you put the required label value in D1. -- David Biddulph "vsoler" wrote in message ... On 5 ago, 21:20, "David Biddulph" <groups [at] biddulph.org.uk wrote: =SUMPRODUCT((A2:A6=1)*B2:B6*C2:C6) The answer is 5, of course, not 6. -- David Biddulph "vsoler" wrote in message ... I have a rectangular range of number, with a row header on top of it. Then I apply a filter. Say that I want the sumproduct of the visible cells in columns B & C A B C 1 1 2 0 2 1 1 3 -1 1 2 3 0 1 2 Then I filter on column A equal to 1 The figure that I need is 1*2+3*(-1)+2*3 = 6 If I use the Sumproduct function I get 10, because the rows starting with 0 are not filtered out. Can I get the result I want with sumproduct? (I think not) Can I use Subtotal instead? without using an auxiliary column neither. Any suggestions? Please, I do not want to use D as an intermediary column for the result of B*C Thank you in advance Thank you David. However, my actual excel model contains labels in column A, not zeroes and ones (and of course the result should be 5) Is there anything that I can do? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT on filtered rows
Try this:
=SUMPRODUCT(SUBTOTAL(3,OFFSET(A2:A6,ROW(A2:A6)-ROW(A2),,1)),B2:B6,C2:C6) -- Biff Microsoft Excel MVP "vsoler" wrote in message ... I have a rectangular range of number, with a row header on top of it. Then I apply a filter. Say that I want the sumproduct of the visible cells in columns B & C A B C 1 1 2 0 2 1 1 3 -1 1 2 3 0 1 2 Then I filter on column A equal to 1 The figure that I need is 1*2+3*(-1)+2*3 = 6 If I use the Sumproduct function I get 10, because the rows starting with 0 are not filtered out. Can I get the result I want with sumproduct? (I think not) Can I use Subtotal instead? without using an auxiliary column neither. Any suggestions? Please, I do not want to use D as an intermediary column for the result of B*C Thank you in advance |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT on filtered rows
On 6 ago, 00:51, "T. Valko" wrote:
Try this: =SUMPRODUCT(SUBTOTAL(3,OFFSET(A2:A6,ROW(A2:A6)-ROW(A2),,1)),B2:B6,C2:C6) -- Biff Microsoft Excel MVP "vsoler" wrote in message ... I have a rectangular range of number, with a row header on top of it. Then I apply a filter. Say that I want the sumproduct of the visible cells in columns B & C A * * B * * C 1 * * 1 * * *2 0 * * 2 * * *1 1 * * 3 * * *-1 1 * * 2 * * *3 0 * * 1 * * *2 Then I filter on column A equal to 1 The figure that I need is *1*2+3*(-1)+2*3 = 6 If I use the Sumproduct function I get 10, because the rows starting with 0 are not filtered out. Can I get the result I want with sumproduct? (I think not) Can I use Subtotal instead? without using an auxiliary column neither. Any suggestions? Please, I do not want to use D as an intermediary column for the result of B*C Thank you in advance VALKO, Great, it works!!! and it is exactly what I was looking for, except perhaps for the volatile function OFFSET which I don't like a lot. Now, it would also be fantastic if I could understand a little bit how it works or at least if only you could give me a hint! Is there any way to replace the OFFSET function by, say, a combination of INDEX and some other functions? Looking forward to hearing from you again. Thank you |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT on filtered rows
=SUMPRODUCT(SUBTOTAL(3,OFFSET(A2:A6,ROW(A2:A6)-ROW(A2),,1)),B2:B6,C2:C6)
SUBTOTAL(3,OFFSET(A2:A6,ROW(A2:A6)-ROW(A2),,1)) That evaluates *one* cell at a time and returns the SUBTOTAL for each cell. If the cell is not empty and is not hidden by the filter the SUBTOTAL for that cell = 1, otherwise the SUBTOTAL for that cell = 0. Thes subtotals are passed to SUMPRODUCT as an array. When the filter is set to Show All the array is made up of all 1s. When the filter is applied on some value in column A then the array is made of up of 0s and 1s. If you filtered on 0 the array would be {0;1;0;0;1}. Then all 3 range arrays are multiplied together: 0*1*2 = 0 1*2*1 = 2 0*3*-1 = 0 0*2*3 = 0 1*1*2 = 2 Then summed for the result: =SUMPRODUCT({0;2;0;0;2}) =4 Is there any way to replace the OFFSET function by, say, a combination of INDEX and some other functions? Not that I can think of. OFFSET allows this to work because it can pass one cell at a time to the SUBTOTAL function which is how the SUBTOTAL function works. INDEX can't do that. At least, I can't think of a way to make it do that. -- Biff Microsoft Excel MVP "vsoler" wrote in message ... On 6 ago, 00:51, "T. Valko" wrote: Try this: =SUMPRODUCT(SUBTOTAL(3,OFFSET(A2:A6,ROW(A2:A6)-ROW(A2),,1)),B2:B6,C2:C6) -- Biff Microsoft Excel MVP "vsoler" wrote in message ... I have a rectangular range of number, with a row header on top of it. Then I apply a filter. Say that I want the sumproduct of the visible cells in columns B & C A B C 1 1 2 0 2 1 1 3 -1 1 2 3 0 1 2 Then I filter on column A equal to 1 The figure that I need is 1*2+3*(-1)+2*3 = 6 If I use the Sumproduct function I get 10, because the rows starting with 0 are not filtered out. Can I get the result I want with sumproduct? (I think not) Can I use Subtotal instead? without using an auxiliary column neither. Any suggestions? Please, I do not want to use D as an intermediary column for the result of B*C Thank you in advance VALKO, Great, it works!!! and it is exactly what I was looking for, except perhaps for the volatile function OFFSET which I don't like a lot. Now, it would also be fantastic if I could understand a little bit how it works or at least if only you could give me a hint! Is there any way to replace the OFFSET function by, say, a combination of INDEX and some other functions? Looking forward to hearing from you again. Thank you |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT on filtered rows
On 7 ago, 08:40, "T. Valko" wrote:
=SUMPRODUCT(SUBTOTAL(3,OFFSET(A2:A6,ROW(A2:A6)-ROW(A2),,1)),B2:B6,C2:C6) SUBTOTAL(3,OFFSET(A2:A6,ROW(A2:A6)-ROW(A2),,1)) That evaluates *one* cell at a time and returns the SUBTOTAL for each cell. If the cell is not empty and is not hidden by the filter the SUBTOTAL for that cell = 1, otherwise the SUBTOTAL for that cell = 0. Thes subtotals are passed to SUMPRODUCT as an array. When the filter is set to Show All the array is made up of all 1s. When the filter is applied on some value in column A then the array is made of up of 0s and 1s. If you filtered on 0 the array would be {0;1;0;0;1}. Then all 3 range arrays are multiplied together: 0*1*2 = 0 1*2*1 = 2 0*3*-1 = 0 0*2*3 = 0 1*1*2 = 2 Then summed for the result: =SUMPRODUCT({0;2;0;0;2}) =4 Is there any way to replace the OFFSET function by, say, a combination of INDEX and some other functions? Not that I can think of. OFFSET allows this to work because it can pass one cell at a time to the SUBTOTAL function which is how the SUBTOTAL function works. INDEX can't do that. At least, I can't think of a way to make it do that. -- Biff Microsoft Excel MVP "vsoler" wrote in message ... On 6 ago, 00:51, "T. Valko" wrote: Try this: =SUMPRODUCT(SUBTOTAL(3,OFFSET(A2:A6,ROW(A2:A6)-ROW(A2),,1)),B2:B6,C2:C6) -- Biff Microsoft Excel MVP "vsoler" wrote in message ... I have a rectangular range of number, with a row header on top of it. Then I apply a filter. Say that I want the sumproduct of the visible cells in columns B & C A B C 1 1 2 0 2 1 1 3 -1 1 2 3 0 1 2 Then I filter on column A equal to 1 The figure that I need is 1*2+3*(-1)+2*3 = 6 If I use the Sumproduct function I get 10, because the rows starting with 0 are not filtered out. Can I get the result I want with sumproduct? (I think not) Can I use Subtotal instead? without using an auxiliary column neither. Any suggestions? Please, I do not want to use D as an intermediary column for the result of B*C Thank you in advance VALKO, Great, it works!!! and it is exactly what I was looking for, except perhaps for the volatile function OFFSET which I don't like a lot. Now, it would also be fantastic if I could understand a little bit how it works or at least if only you could give me a hint! Is there any way to replace the OFFSET function by, say, a combination of INDEX and some other functions? Looking forward to hearing from you again. Thank you Thank you very much for your answer, T. Valko. Your knowledge about how functions work goes far beyond what's described in most technical books about excel. Just as a comment: after having "played" with excel's functions for years, more and more I feel that the power of excel is in undocumented or hidden features that hide to the user the solution to relatively simple problems. The solutions or work arounds proposed in the newsgroups seem to have nothing to do with elegant, simple or mathematics oriented algebra or even logics. That is someting that I definedly do not like of excel formulas. However, I could not live, work or have fun without excel. The more I think, the more I am convinced that the problem resides in the backwards compatibility of excel, that it prevents it from evolving. Version after version we see that what changes in Excel is how colors are handled, filters, optical effects and the like. But its ability of number crunching stays the same, with no changes year after year. Because I am a fan of spreadsheets, I have given a lot of thought to what a new spreadsheet should have, or at least how the formula calculation engine should work. But I have not found any forum or newsgroup where to discuss them. Anyway, while thinking of the future evolutions of the tool, I will keep my beloved excel. At least it is something that I have. Thank you again T. Valko |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT on filtered rows
Wow!
I completely agree with everything you said. Especially this: Version after version we see that what changes in Excel is how colors are handled, filters, optical effects and the like. But its ability of number crunching stays the same, with no changes year after year. In my opinion, developing functions is probably the easiest thing to do yet, as you note, version after version there aren't very many new functions introduced. I can think of literally dozens of useful functions that Excel is sorely missing. And because they're missing, we have to come up with convoluted work-arounds in many cases. -- Biff Microsoft Excel MVP "vsoler" wrote in message ... On 7 ago, 08:40, "T. Valko" wrote: =SUMPRODUCT(SUBTOTAL(3,OFFSET(A2:A6,ROW(A2:A6)-ROW(A2),,1)),B2:B6,C2:C6) SUBTOTAL(3,OFFSET(A2:A6,ROW(A2:A6)-ROW(A2),,1)) That evaluates *one* cell at a time and returns the SUBTOTAL for each cell. If the cell is not empty and is not hidden by the filter the SUBTOTAL for that cell = 1, otherwise the SUBTOTAL for that cell = 0. Thes subtotals are passed to SUMPRODUCT as an array. When the filter is set to Show All the array is made up of all 1s. When the filter is applied on some value in column A then the array is made of up of 0s and 1s. If you filtered on 0 the array would be {0;1;0;0;1}. Then all 3 range arrays are multiplied together: 0*1*2 = 0 1*2*1 = 2 0*3*-1 = 0 0*2*3 = 0 1*1*2 = 2 Then summed for the result: =SUMPRODUCT({0;2;0;0;2}) =4 Is there any way to replace the OFFSET function by, say, a combination of INDEX and some other functions? Not that I can think of. OFFSET allows this to work because it can pass one cell at a time to the SUBTOTAL function which is how the SUBTOTAL function works. INDEX can't do that. At least, I can't think of a way to make it do that. -- Biff Microsoft Excel MVP "vsoler" wrote in message ... On 6 ago, 00:51, "T. Valko" wrote: Try this: =SUMPRODUCT(SUBTOTAL(3,OFFSET(A2:A6,ROW(A2:A6)-ROW(A2),,1)),B2:B6,C2:C6) -- Biff Microsoft Excel MVP "vsoler" wrote in message ... I have a rectangular range of number, with a row header on top of it. Then I apply a filter. Say that I want the sumproduct of the visible cells in columns B & C A B C 1 1 2 0 2 1 1 3 -1 1 2 3 0 1 2 Then I filter on column A equal to 1 The figure that I need is 1*2+3*(-1)+2*3 = 6 If I use the Sumproduct function I get 10, because the rows starting with 0 are not filtered out. Can I get the result I want with sumproduct? (I think not) Can I use Subtotal instead? without using an auxiliary column neither. Any suggestions? Please, I do not want to use D as an intermediary column for the result of B*C Thank you in advance VALKO, Great, it works!!! and it is exactly what I was looking for, except perhaps for the volatile function OFFSET which I don't like a lot. Now, it would also be fantastic if I could understand a little bit how it works or at least if only you could give me a hint! Is there any way to replace the OFFSET function by, say, a combination of INDEX and some other functions? Looking forward to hearing from you again. Thank you Thank you very much for your answer, T. Valko. Your knowledge about how functions work goes far beyond what's described in most technical books about excel. Just as a comment: after having "played" with excel's functions for years, more and more I feel that the power of excel is in undocumented or hidden features that hide to the user the solution to relatively simple problems. The solutions or work arounds proposed in the newsgroups seem to have nothing to do with elegant, simple or mathematics oriented algebra or even logics. That is someting that I definedly do not like of excel formulas. However, I could not live, work or have fun without excel. The more I think, the more I am convinced that the problem resides in the backwards compatibility of excel, that it prevents it from evolving. Version after version we see that what changes in Excel is how colors are handled, filters, optical effects and the like. But its ability of number crunching stays the same, with no changes year after year. Because I am a fan of spreadsheets, I have given a lot of thought to what a new spreadsheet should have, or at least how the formula calculation engine should work. But I have not found any forum or newsgroup where to discuss them. Anyway, while thinking of the future evolutions of the tool, I will keep my beloved excel. At least it is something that I have. Thank you again T. Valko |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SumProduct and Sum only Filtered data | Excel Discussion (Misc queries) | |||
How to sumproduct only filtered data | Excel Discussion (Misc queries) | |||
Cut filtered rows, paste into next empty row of new sheet, and delete cut rows | Excel Worksheet Functions | |||
Help!! I have problem deleting 2500 rows of filtered rows!!!! | Excel Discussion (Misc queries) | |||
Sumproduct on filtered cells | Excel Worksheet Functions |