Subtotal on Autofilter with Countif

Can this be done, I already have the formula to provide me a count on text
when a column is filtered, the bad thing is that I need it to not count a
cell if it's a Zero. This is the formula I have

=SUMPRODUCT((SUBTOTAL(3,OFFSET(F$6,ROW(F7:F1001)-ROW(F6),,1))))

Now, on that range F7:F1001, The label is March, I need to analyze inventory
turns by company on an item, so If I filter the company to say Dell, I want
to see the items and how many turns as a total we had in March, but it's
counting Zeros too. Can this be done?

Maybe

=SUMPRODUCT(--(F7:F1001<>0),--SUBTOTAL(3,OFFSET(F$6,ROW(F7:F1001)-ROW(F6),,1)))


Regards,


Peo Sjoblom


"JavyD" <JavyD@discussions.microsoft.com> wrote in message
news:B203D05A-FE5A-4B97-A47B-E7EE4F5D4C24@microsoft.com...
> Can this be done, I already have the formula to provide me a count on text
> when a column is filtered, the bad thing is that I need it to not count a
> cell if it's a Zero. This is the formula I have
>
> =SUMPRODUCT((SUBTOTAL(3,OFFSET(F$6,ROW(F7:F1001)-ROW(F6),,1))))
>
> Now, on that range F7:F1001, The label is March, I need to analyze
> inventory
> turns by company on an item, so If I filter the company to say Dell, I
> want
> to see the items and how many turns as a total we had in March, but it's
> counting Zeros too. Can this be done?

Simply amazing. Peo, what are those --, one thing is getting the solution,
but I want to learn the magic trick.

"Peo Sjoblom" wrote:

> Maybe
>
> =SUMPRODUCT(--(F7:F1001<>0),--SUBTOTAL(3,OFFSET(F$6,ROW(F7:F1001)-ROW(F6),,1)))
>
>
> Regards,
>
>
> Peo Sjoblom
>
>
> "JavyD" <JavyD@discussions.microsoft.com> wrote in message
> news:B203D05A-FE5A-4B97-A47B-E7EE4F5D4C24@microsoft.com...
> > Can this be done, I already have the formula to provide me a count on text
> > when a column is filtered, the bad thing is that I need it to not count a
> > cell if it's a Zero. This is the formula I have
> >
> > =SUMPRODUCT((SUBTOTAL(3,OFFSET(F$6,ROW(F7:F1001)-ROW(F6),,1))))
> >
> > Now, on that range F7:F1001, The label is March, I need to analyze
> > inventory
> > turns by company on an item, so If I filter the company to say Dell, I
> > want
> > to see the items and how many turns as a total we had in March, but it's
> > counting Zeros too. Can this be done?
>
>
>

The unary minuses just coerces TRUE/FALSE values into 1/0 thus you can use
the built in format of SUMPRODUCT which has some benefits, for instance if
you would use

((range1=criteria)*(range2))

and there would be a text value in range2 like for instance a blank from an
IF function ="" then the formula would result in a #VALUE! error whereas

(--(range1=criteria),(range2))

would not

Of course you can use anything like 0+ or 1* but I find it looking less
intrusive and I also heard it might be a few nano seconds faster <bg>


Peo


"JavyD" <JavyD@discussions.microsoft.com> wrote in message
news:6E05B27C-A001-4380-B1DE-2248E555E0D0@microsoft.com...
> Simply amazing. Peo, what are those --, one thing is getting the
> solution,
> but I want to learn the magic trick.
>
> "Peo Sjoblom" wrote:
>
>> Maybe
>>
>> =SUMPRODUCT(--(F7:F1001<>0),--SUBTOTAL(3,OFFSET(F$6,ROW(F7:F1001)-ROW(F6),,1)))
>>
>>
>> Regards,
>>
>>
>> Peo Sjoblom
>>
>>
>> "JavyD" <JavyD@discussions.microsoft.com> wrote in message
>> news:B203D05A-FE5A-4B97-A47B-E7EE4F5D4C24@microsoft.com...
>> > Can this be done, I already have the formula to provide me a count on
>> > text
>> > when a column is filtered, the bad thing is that I need it to not count
>> > a
>> > cell if it's a Zero. This is the formula I have
>> >
>> > =SUMPRODUCT((SUBTOTAL(3,OFFSET(F$6,ROW(F7:F1001)-ROW(F6),,1))))
>> >
>> > Now, on that range F7:F1001, The label is March, I need to analyze
>> > inventory
>> > turns by company on an item, so If I filter the company to say Dell, I
>> > want
>> > to see the items and how many turns as a total we had in March, but
>> > it's
>> > counting Zeros too. Can this be done?
>>
>>
>>

Bravooo. Thanks for your help!

"Peo Sjoblom" wrote:

> The unary minuses just coerces TRUE/FALSE values into 1/0 thus you can use
> the built in format of SUMPRODUCT which has some benefits, for instance if
> you would use
>
> ((range1=criteria)*(range2))
>
> and there would be a text value in range2 like for instance a blank from an
> IF function ="" then the formula would result in a #VALUE! error whereas
>
> (--(range1=criteria),(range2))
>
> would not
>
> Of course you can use anything like 0+ or 1* but I find it looking less
> intrusive and I also heard it might be a few nano seconds faster <bg>
>
>
> Peo
>
>
> "JavyD" <JavyD@discussions.microsoft.com> wrote in message
> news:6E05B27C-A001-4380-B1DE-2248E555E0D0@microsoft.com...
> > Simply amazing. Peo, what are those --, one thing is getting the
> > solution,
> > but I want to learn the magic trick.
> >
> > "Peo Sjoblom" wrote:
> >
> >> Maybe
> >>
> >> =SUMPRODUCT(--(F7:F1001<>0),--SUBTOTAL(3,OFFSET(F$6,ROW(F7:F1001)-ROW(F6),,1)))
> >>
> >>
> >> Regards,
> >>
> >>
> >> Peo Sjoblom
> >>
> >>
> >> "JavyD" <JavyD@discussions.microsoft.com> wrote in message
> >> news:B203D05A-FE5A-4B97-A47B-E7EE4F5D4C24@microsoft.com...
> >> > Can this be done, I already have the formula to provide me a count on
> >> > text
> >> > when a column is filtered, the bad thing is that I need it to not count
> >> > a
> >> > cell if it's a Zero. This is the formula I have
> >> >
> >> > =SUMPRODUCT((SUBTOTAL(3,OFFSET(F$6,ROW(F7:F1001)-ROW(F6),,1))))
> >> >
> >> > Now, on that range F7:F1001, The label is March, I need to analyze
> >> > inventory
> >> > turns by company on an item, so If I filter the company to say Dell, I
> >> > want
> >> > to see the items and how many turns as a total we had in March, but
> >> > it's
> >> > counting Zeros too. Can this be done?
> >>
> >>
> >>
>
>
>