Less cluttered than my offering.

--
Don Guillett
SalesAid Software

"JMB" wrote in message
...
you could try:
=SUMPRODUCT(--(B2:B10="x"),SUBTOTAL(3,OFFSET(B2,ROW(B2:B10)-ROW(B2),0)))

normally entered


"mark" wrote:

Hi. Someone just called me about a formula that one of the managers
thinks
he needs. I can do what they want in three rows, but am not seeing how
to do
it in one row, and have it change with the AutoFilter.

They have something like the following, across rows and columns:
Schedule Value
Row1 1 X
Row2 2 C
Row3 3 NULL
Row4 1 NULL
Row5 2 X
Row6 3 NULL

They want to count the instances of X for each schedule, where AutoFilter
is
turned on, and they pick schedule 1, 2 , or 3, from the drop down.

I can give them an array formula based upon another cell, say A12, that
will
do it:

=SUM(--(B2:B9="X")*--(A2:A9=A12))

But in that example, you have to type the 1, 2, or 3 in cell A12... that
is
not automatically picked up from the filtered selection.

I tried combining the array formula above with a subtotal(9,), but I
didn't
get that to enter with the array. Perhaps I just had a syntax problem.

Suggestions?

Thanks.
Mark