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

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

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

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

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