On 6 ago, 00:51, "T. Valko" wrote:
Try this:

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

--
Biff
Microsoft Excel MVP

"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

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