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?

Subtotal on Autofilter with Countif
 

Maybe

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


Regards,


Peo Sjoblom


"JavyD" wrote in message
...
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?

Subtotal on Autofilter with Countif
 

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" wrote in message
...
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?

Subtotal on Autofilter with Countif
 

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" wrote in message
...
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" wrote in message
...
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?

Subtotal on Autofilter with Countif
 

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" wrote in message
...
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" wrote in message
...
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?