SUMPRODUCT on filtered rows

[vsoler]

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

 

[vsoler]

=SUMPRODUCT((A2:A6=1)*B2:B6*C2:C6)

The answer is 5, of course, not 6.

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?

 

[T. Valko]

Try this:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(A2:A6,ROW(A2:A6)-ROW(A2),,1)),B2:B6,C2:C6)

 

[vsoler]

Try this:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(A2:A6,ROW(A2:A6)-ROW(A2),,1)),B2:B6,C2:C6)

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

 

[T. Valko]

=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

Try this:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(A2:A6,ROW(A2:A6)-ROW(A2),,1)),B2:B6,C2:C6)

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

 

[vsoler]

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


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





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

 

[T. Valko]

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.