Hi Roger,

Problem is that it does handle filtered rows, but not rows hidden by some
other method. I posted a solution that incorporated a UDF for that case a
few days ago, which also handles the filtered data. Might just use that as
my standard.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Roger Govier" wrote in message
...
Hi Bob

That's cool.
Never thought about using the Subtotal function inside Sumproduct like
that.

Regards

Roger Govier


Bob Phillips wrote:
Here is an example where B2:b20 is filtered, and a condition in A2:A20
is
tested


=SUMPRODUCT((SUBTOTAL(3,OFFSET($B$1,ROW($B$2:$B$20 )-ROW($B$1),,1)))*(C2:C20=
"a"))