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

try this array entered formula to find and use the value in the first
visible cell
=SUM(--(B2:B100="x")*--(A2:A100=INDEX(A2:A100,MATCH(1,SUBTOTAL(3,OFFSET(A2:A100,ROW(A2:A100)-ROW(A2),0,1)),0))))

--
Don Guillett
SalesAid Software
(E-Mail Removed)
"mark" <(E-Mail Removed)> wrote in message
news:CD6BF2D9-FE68-4626-9187-(E-Mail Removed)...
> 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
>

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
>

Less cluttered than my offering.

--
Don Guillett
SalesAid Software
(E-Mail Removed)
"JMB" <(E-Mail Removed)> wrote in message
news:AA6C2887-96CD-4667-85B2-(E-Mail Removed)...
> 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
>>

Thank you both. I will try your suggestion on Monday when I get back to it,
if not later today.

I recognize your names, and am sure from that that your advice is good.

Thank you.
Mark

"Don Guillett" wrote:

> Less cluttered than my offering.
>
> --
> Don Guillett
> SalesAid Software
> (E-Mail Removed)
> "JMB" <(E-Mail Removed)> wrote in message
> news:AA6C2887-96CD-4667-85B2-(E-Mail Removed)...
> > 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
> >>
>
>

Both should give you the same desired result

--
Don Guillett
SalesAid Software
(E-Mail Removed)
"mark" <(E-Mail Removed)> wrote in message
news62E00D7-C2E0-4FE5-ADD9-(E-Mail Removed)...
> Thank you both. I will try your suggestion on Monday when I get back to
> it,
> if not later today.
>
> I recognize your names, and am sure from that that your advice is good.
>
> Thank you.
> Mark
>
> "Don Guillett" wrote:
>
>> Less cluttered than my offering.
>>
>> --
>> Don Guillett
>> SalesAid Software
>> (E-Mail Removed)
>> "JMB" <(E-Mail Removed)> wrote in message
>> news:AA6C2887-96CD-4667-85B2-(E-Mail Removed)...
>> > 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
>> >>
>>
>>