Subtotal on Autofilter with Countif

[Guest]

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?

 

[Peo Sjoblom]

Maybe

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


Regards,


Peo Sjoblom

 

[Guest]

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

 

[Peo Sjoblom]

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

 

[Guest]

Bravooo. Thanks for your help!

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