software

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:F1001lt;gt;0),--SUBTOTAL(3,OFFSET(F$6,ROW(F7:F1001)-ROW(F6),,1)))Regards,Peo Sjoblomquot;JavyDquot; gt; wrote in message
...
gt; Can this be done, I already have the formula to provide me a count on text
gt; when a column is filtered, the bad thing is that I need it to not count a
gt; cell if it's a Zero. This is the formula I have
gt;
gt; =SUMPRODUCT((SUBTOTAL(3,OFFSET(F$6,ROW(F7:F1001)-ROW(F6),,1))))
gt;
gt; Now, on that range F7:F1001, The label is March, I need to analyze
gt; inventory
gt; turns by company on an item, so If I filter the company to say Dell, I
gt; want
gt; to see the items and how many turns as a total we had in March, but it's
gt; 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.

quot;Peo Sjoblomquot; wrote:

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

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 =quot;quot; 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 lt;bggt;Peoquot;JavyDquot; gt; wrote in message
...
gt; Simply amazing. Peo, what are those --, one thing is getting the
gt; solution,
gt; but I want to learn the magic trick.
gt;
gt; quot;Peo Sjoblomquot; wrote:
gt;
gt;gt; Maybe
gt;gt;
gt;gt; =SUMPRODUCT(--(F7:F1001lt;gt;0),--SUBTOTAL(3,OFFSET(F$6,ROW(F7:F1001)-ROW(F6),,1)))
gt;gt;
gt;gt;
gt;gt; Regards,
gt;gt;
gt;gt;
gt;gt; Peo Sjoblom
gt;gt;
gt;gt;
gt;gt; quot;JavyDquot; gt; wrote in message
gt;gt; ...
gt;gt; gt; Can this be done, I already have the formula to provide me a count on
gt;gt; gt; text
gt;gt; gt; when a column is filtered, the bad thing is that I need it to not count
gt;gt; gt; a
gt;gt; gt; cell if it's a Zero. This is the formula I have
gt;gt; gt;
gt;gt; gt; =SUMPRODUCT((SUBTOTAL(3,OFFSET(F$6,ROW(F7:F1001)-ROW(F6),,1))))
gt;gt; gt;
gt;gt; gt; Now, on that range F7:F1001, The label is March, I need to analyze
gt;gt; gt; inventory
gt;gt; gt; turns by company on an item, so If I filter the company to say Dell, I
gt;gt; gt; want
gt;gt; gt; to see the items and how many turns as a total we had in March, but
gt;gt; gt; it's
gt;gt; gt; counting Zeros too. Can this be done?
gt;gt;
gt;gt;
gt;gt;
Bravooo. Thanks for your help!

quot;Peo Sjoblomquot; wrote:

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