Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
For Mike H.'s reply about my msg Jan 20, 'sumif but also filtering
I apologize in advance from everybody if my this mail is aginst general rules
of the forum. I' m new in the forum (~1-2 months old), so I do not know much about the rules. He wrote a formula for me My question was "sumif but there may be also filterings" =SUMPRODUCT(--(C1:C10="$");SUBTOTAL(9;OFFSET(B1:B10;ROW(B1:B10)-MIN(ROW(B1:B10));0;1))) His formula is working wonderfully. I am a medium level excel user in general level office environments. for i = 1 to 20 I looked at the formula, worked on it to understand how it works splitted the formula into pieces etc next i I could not solve that offset part, why, how ? I will make i=50:), and go on trying to understand it. Then I recognized that I can see the profiles of people and looked Mike' s profile and then understood everything. He is a 40 years worked, retired chemical engineer. I' m sure he is 'inititaing a chemical reaction in excel' with this formula : ) and therefore I can not see, understand how the formula is working. Thanks and best regards, Cousin Excel |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
For Mike H.'s reply about my msg Jan 20, 'sumif but also filtering
Hi,
So you want to understand how the formula works. Lets take a shortened version that looks at 5 rows only where row 3 is hidden by an auto filter. Note i've had to change back to , instead of ; for my excel version =SUMPRODUCT(--(C2:C6="$"),SUBTOTAL(9,OFFSET(B2:B6,ROW(B2:B6)-MIN(ROW(B2:B6)),0,1))) The formula is basically producing 3 arrays that sumproduct will multiply together --(C1:C6="$") Because all my cells contain $ it produces this array. i.e 5 TRUE which the double unary (--) convert to a numeric value {1;1;1;1;1} OFFSET(B2:B6,ROW(B2:B6)-MIN(ROW(B2:B6)),0,1) Produces the array which are the numbers in my cells {6;7;8;9;10} Now we wrap that in a subtotal function where hidden rows evaluate as 0 and visible rows as 1 we get the following array SUBTOTAL(9,OFFSET(B2:B6,ROW(B2:B6)-MIN(ROW(B2:B6)),0,1)) Because row 3 is hidden it produces the array {6;7;0;9;10} Now we have the 3 arrays we need and Sumproduct will multiply them together {1;1;1;1;1} {6;7;8;9;10} {6;7;0;9;10} Hope this helps -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "CousinExcel" wrote: I apologize in advance from everybody if my this mail is aginst general rules of the forum. I' m new in the forum (~1-2 months old), so I do not know much about the rules. He wrote a formula for me My question was "sumif but there may be also filterings" =SUMPRODUCT(--(C1:C10="$");SUBTOTAL(9;OFFSET(B1:B10;ROW(B1:B10)-MIN(ROW(B1:B10));0;1))) His formula is working wonderfully. I am a medium level excel user in general level office environments. for i = 1 to 20 I looked at the formula, worked on it to understand how it works splitted the formula into pieces etc next i I could not solve that offset part, why, how ? I will make i=50:), and go on trying to understand it. Then I recognized that I can see the profiles of people and looked Mike' s profile and then understood everything. He is a 40 years worked, retired chemical engineer. I' m sure he is 'inititaing a chemical reaction in excel' with this formula : ) and therefore I can not see, understand how the formula is working. Thanks and best regards, Cousin Excel |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
For Mike H.'s reply about my msg Jan 20, 'sumif but also filte
By way of clarification.
3 arrays are produced but because of the way the formula is constructed the sumproduct bit only works on 2 of them {1;1;1;1;1} {6;7;0;9;10} 1*6=6 1*7=7 1*0=0 1*9=9 1*10=10 =32 -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Mike H" wrote: Hi, So you want to understand how the formula works. Lets take a shortened version that looks at 5 rows only where row 3 is hidden by an auto filter. Note i've had to change back to , instead of ; for my excel version =SUMPRODUCT(--(C2:C6="$"),SUBTOTAL(9,OFFSET(B2:B6,ROW(B2:B6)-MIN(ROW(B2:B6)),0,1))) The formula is basically producing 3 arrays that sumproduct will multiply together --(C1:C6="$") Because all my cells contain $ it produces this array. i.e 5 TRUE which the double unary (--) convert to a numeric value {1;1;1;1;1} OFFSET(B2:B6,ROW(B2:B6)-MIN(ROW(B2:B6)),0,1) Produces the array which are the numbers in my cells {6;7;8;9;10} Now we wrap that in a subtotal function where hidden rows evaluate as 0 and visible rows as 1 we get the following array SUBTOTAL(9,OFFSET(B2:B6,ROW(B2:B6)-MIN(ROW(B2:B6)),0,1)) Because row 3 is hidden it produces the array {6;7;0;9;10} Now we have the 3 arrays we need and Sumproduct will multiply them together {1;1;1;1;1} {6;7;8;9;10} {6;7;0;9;10} Hope this helps -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "CousinExcel" wrote: I apologize in advance from everybody if my this mail is aginst general rules of the forum. I' m new in the forum (~1-2 months old), so I do not know much about the rules. He wrote a formula for me My question was "sumif but there may be also filterings" =SUMPRODUCT(--(C1:C10="$");SUBTOTAL(9;OFFSET(B1:B10;ROW(B1:B10)-MIN(ROW(B1:B10));0;1))) His formula is working wonderfully. I am a medium level excel user in general level office environments. for i = 1 to 20 I looked at the formula, worked on it to understand how it works splitted the formula into pieces etc next i I could not solve that offset part, why, how ? I will make i=50:), and go on trying to understand it. Then I recognized that I can see the profiles of people and looked Mike' s profile and then understood everything. He is a 40 years worked, retired chemical engineer. I' m sure he is 'inititaing a chemical reaction in excel' with this formula : ) and therefore I can not see, understand how the formula is working. Thanks and best regards, Cousin Excel |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
For Mike H.'s reply about my msg Jan 20, 'sumif but also filte
Thank you Sir Master,
I am eager to study your explanations after the work. Thanks and best regards, Cousin Excel "Mike H" wrote: By way of clarification. 3 arrays are produced but because of the way the formula is constructed the sumproduct bit only works on 2 of them {1;1;1;1;1} {6;7;0;9;10} 1*6=6 1*7=7 1*0=0 1*9=9 1*10=10 =32 -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Mike H" wrote: Hi, So you want to understand how the formula works. Lets take a shortened version that looks at 5 rows only where row 3 is hidden by an auto filter. Note i've had to change back to , instead of ; for my excel version =SUMPRODUCT(--(C2:C6="$"),SUBTOTAL(9,OFFSET(B2:B6,ROW(B2:B6)-MIN(ROW(B2:B6)),0,1))) The formula is basically producing 3 arrays that sumproduct will multiply together --(C1:C6="$") Because all my cells contain $ it produces this array. i.e 5 TRUE which the double unary (--) convert to a numeric value {1;1;1;1;1} OFFSET(B2:B6,ROW(B2:B6)-MIN(ROW(B2:B6)),0,1) Produces the array which are the numbers in my cells {6;7;8;9;10} Now we wrap that in a subtotal function where hidden rows evaluate as 0 and visible rows as 1 we get the following array SUBTOTAL(9,OFFSET(B2:B6,ROW(B2:B6)-MIN(ROW(B2:B6)),0,1)) Because row 3 is hidden it produces the array {6;7;0;9;10} Now we have the 3 arrays we need and Sumproduct will multiply them together {1;1;1;1;1} {6;7;8;9;10} {6;7;0;9;10} Hope this helps -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "CousinExcel" wrote: I apologize in advance from everybody if my this mail is aginst general rules of the forum. I' m new in the forum (~1-2 months old), so I do not know much about the rules. He wrote a formula for me My question was "sumif but there may be also filterings" =SUMPRODUCT(--(C1:C10="$");SUBTOTAL(9;OFFSET(B1:B10;ROW(B1:B10)-MIN(ROW(B1:B10));0;1))) His formula is working wonderfully. I am a medium level excel user in general level office environments. for i = 1 to 20 I looked at the formula, worked on it to understand how it works splitted the formula into pieces etc next i I could not solve that offset part, why, how ? I will make i=50:), and go on trying to understand it. Then I recognized that I can see the profiles of people and looked Mike' s profile and then understood everything. He is a 40 years worked, retired chemical engineer. I' m sure he is 'inititaing a chemical reaction in excel' with this formula : ) and therefore I can not see, understand how the formula is working. Thanks and best regards, Cousin Excel |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
For Mike H.'s reply about my msg Jan 20, 'sumif but also filte
Thank you Master Mike.
If I have not tired you enough, I have question I can not understand OFFSET part. I try to run the OFFSET part for B2: to B6. If I'm not making mistake (I'm sure I'm making), creating 6 elements of the array: 1) OFFSET(B2:B2;row(B2:B2)-min(row(b2:b2));0;1) =OFFSET(B2:B2;2-min(2));0;1) =OFFSET(B2:B2;0;0;1)=6 2) OFFSET(B3:B3;row(B3:B3)-min(row(b3:b3));0;1) =OFFSET(B3:B3;3-min(3));0;1) =OFFSET(B3:B3;0;0;1)=7 ....goes on. But if this is corrrect, then row(...)-min(row(..)) part is always zero. Then, there will not be need for this part. But, if I omit even only the word "min" I get wrong result, so it is needed, so the way I run above is wrong. I tired to find myself and not to tire you but I could not succeed. Thanks and best regards, Cousin Excel "Mike H" wrote: By way of clarification. 3 arrays are produced but because of the way the formula is constructed the sumproduct bit only works on 2 of them {1;1;1;1;1} {6;7;0;9;10} 1*6=6 1*7=7 1*0=0 1*9=9 1*10=10 =32 -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Mike H" wrote: Hi, So you want to understand how the formula works. Lets take a shortened version that looks at 5 rows only where row 3 is hidden by an auto filter. Note i've had to change back to , instead of ; for my excel version =SUMPRODUCT(--(C2:C6="$"),SUBTOTAL(9,OFFSET(B2:B6,ROW(B2:B6)-MIN(ROW(B2:B6)),0,1))) The formula is basically producing 3 arrays that sumproduct will multiply together --(C1:C6="$") Because all my cells contain $ it produces this array. i.e 5 TRUE which the double unary (--) convert to a numeric value {1;1;1;1;1} OFFSET(B2:B6,ROW(B2:B6)-MIN(ROW(B2:B6)),0,1) Produces the array which are the numbers in my cells {6;7;8;9;10} Now we wrap that in a subtotal function where hidden rows evaluate as 0 and visible rows as 1 we get the following array SUBTOTAL(9,OFFSET(B2:B6,ROW(B2:B6)-MIN(ROW(B2:B6)),0,1)) Because row 3 is hidden it produces the array {6;7;0;9;10} Now we have the 3 arrays we need and Sumproduct will multiply them together {1;1;1;1;1} {6;7;8;9;10} {6;7;0;9;10} Hope this helps -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "CousinExcel" wrote: I apologize in advance from everybody if my this mail is aginst general rules of the forum. I' m new in the forum (~1-2 months old), so I do not know much about the rules. He wrote a formula for me My question was "sumif but there may be also filterings" =SUMPRODUCT(--(C1:C10="$");SUBTOTAL(9;OFFSET(B1:B10;ROW(B1:B10)-MIN(ROW(B1:B10));0;1))) His formula is working wonderfully. I am a medium level excel user in general level office environments. for i = 1 to 20 I looked at the formula, worked on it to understand how it works splitted the formula into pieces etc next i I could not solve that offset part, why, how ? I will make i=50:), and go on trying to understand it. Then I recognized that I can see the profiles of people and looked Mike' s profile and then understood everything. He is a 40 years worked, retired chemical engineer. I' m sure he is 'inititaing a chemical reaction in excel' with this formula : ) and therefore I can not see, understand how the formula is working. Thanks and best regards, Cousin Excel |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sumif but additionally there maybe filtering | Excel Discussion (Misc queries) | |||
Mike H - Help | Excel Worksheet Functions | |||
F.A.O Mike H | Excel Discussion (Misc queries) | |||
SUMIF formula while filtering | Excel Worksheet Functions | |||
Mike | Excel Discussion (Misc queries) |