From: JoseBagg on

SUBTOTAL(3,OFFSET(L2:L100,ROW(L2:L100)-ROW(L2),0,1)

This formula is from Domenic. It is part of the formula used to sum
data while ignoring hidden rows. However, this part is not working for
me in excel 2007.

Highly likely, that I am the cause of the problem. Does anyone have
any help for me?


--
JoseBagg
------------------------------------------------------------------------
JoseBagg's Profile: 1591
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=185669

[url="http://www.thecodecage.com/forumz/"]Excel Live Chat[/url]

From: T. Valko on
>this part is not working for me in excel 2007.
>SUBTOTAL(3...

That would only work for rows that are hidden by using a filter. Try using:

SUBTOTAL(103...

The 100 series arguments are for rows that are hidden by some other means
like hiding them manually or hiding them with a macro procedure.

If that doesn't solve the problem then you'll have to post the entire
formula and explain in more detail what you're trying to do.

--
Biff
Microsoft Excel MVP


"JoseBagg" <JoseBagg.47iow2(a)thecodecage.com> wrote in message
news:JoseBagg.47iow2(a)thecodecage.com...
>
> SUBTOTAL(3,OFFSET(L2:L100,ROW(L2:L100)-ROW(L2),0,1)
>
> This formula is from Domenic. It is part of the formula used to sum
> data while ignoring hidden rows. However, this part is not working for
> me in excel 2007.
>
> Highly likely, that I am the cause of the problem. Does anyone have
> any help for me?
>
>
> --
> JoseBagg
> ------------------------------------------------------------------------
> JoseBagg's Profile: 1591
> View this thread:
> http://www.thecodecage.com/forumz/showthread.php?t=185669
>
> [url=&quot;http://www.thecodecage.com/forumz/&quot;]Excel Live Chat[/url]
>


From: JoseBagg on

=SUMPRODUCT(SUBTOTAL(3,OFFSET(L2:L100,ROW(L2:L100)-ROW(L2),0,1)),--(L2:L100="Open"))

This is the larger formual posted by Domenic in another thread.

The part of the this formula that is supposed to create an array of 1's
and 0's where 1 indicates a visible cell and zero indicates a hidden
cell is

=(SUBTOTAL(3,OFFSET(L2:L100,ROW(L2:L100)-ROW(L2),0,1))


However, when I evalute this in excel 2007, it does not evaluate to a
nice array like it should, it just evaluates to a single number.


--
JoseBagg
------------------------------------------------------------------------
JoseBagg's Profile: 1591
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=185669

[url=&quot;http://www.thecodecage.com/forumz/&quot;]Excel Live Chat[/url]

From: T. Valko on
>The part of the this formula that is supposed to
>create an array of 1's and 0's where 1 indicates
>a visible cell and zero indicates a hidden cell is
>=(SUBTOTAL(3,OFFSET(L2:L100,ROW(L2:L100)-ROW(L2),0,1))
>However, when I evalute this in excel 2007, it
>does not evaluate to a nice array like it should,
>it just evaluates to a single number.

If you enter that formula in a single cell then it will return only a single
result. That part of the formula is processing the arguments as an ARRAY. To
see each result of the array you'd need to select a range of cells the same
size as L2:L100 then enter the formula as an array.

If you want to see the array without having to enter the formula in a bunch
of cells, highlight that portion of the formula *in the formula bar* then
press function key F9.

Another option is to select the formula cell>goto the Formulas tab>Formula
Auditing>Evaluate Formula. Click the Evaluate button repeatedly to see how
Excel calculates the formula one step at a time. I use this feature often so
I added this to my QAT.

--
Biff
Microsoft Excel MVP


"JoseBagg" <JoseBagg.47irnj(a)thecodecage.com> wrote in message
news:JoseBagg.47irnj(a)thecodecage.com...
>
> =SUMPRODUCT(SUBTOTAL(3,OFFSET(L2:L100,ROW(L2:L100)-ROW(L2),0,1)),--(L2:L100="Open"))
>
> This is the larger formual posted by Domenic in another thread.
>
> The part of the this formula that is supposed to create an array of 1's
> and 0's where 1 indicates a visible cell and zero indicates a hidden
> cell is
>
> =(SUBTOTAL(3,OFFSET(L2:L100,ROW(L2:L100)-ROW(L2),0,1))
>
>
> However, when I evalute this in excel 2007, it does not evaluate to a
> nice array like it should, it just evaluates to a single number.
>
>
> --
> JoseBagg
> ------------------------------------------------------------------------
> JoseBagg's Profile: 1591
> View this thread:
> http://www.thecodecage.com/forumz/showthread.php?t=185669
>
> [url=&quot;http://www.thecodecage.com/forumz/&quot;]Excel Live Chat[/url]
>


From: JoseBagg on

Thanks, I get it now.


--
JoseBagg
------------------------------------------------------------------------
JoseBagg's Profile: 1591
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=185669

[url=&quot;http://www.thecodecage.com/forumz/&quot;]Excel Live Chat[/url]