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?