For Mike H.'s reply about my msg Jan 20, 'sumif but also filtering

[CousinExcel]

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

 

[Mike H]

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.

 

[Mike H]

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.

 

[CousinExcel]

Thank you Sir Master,
I am eager to study your explanations after the work.
Thanks and best regards,
Cousin Excel

 

[CousinExcel]

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