Re: How do you ignore hidden rows in a SUMIF() function?

[Domenic]

Try...

=SUMPRODUCT(SUBTOTAL(3,OFFSET(EH5:EH2001,ROW(EH5:EH2001)-ROW(EH5),0,1)),-
-(EH5:EH2001<0),EH5:EH2001)

Hope this helps!

In article <E134A2C3-D8A8-4BD9...@microsoft.com>,
"Gerry" <Ge...@discussions.microsoft.com> wrote:

> I would like to use the SUMIF function to sum all negative cash flows
> (=SUMIF('Worksheet A - Info & Gross IRR'!EH5:EH2001,"<0")) in a given column
> of data with hidden values (the data is in rows that have been hidden using
> Autofilter and certian criteria). I would like the SUMIF function to ignore
> the the hidden values (transactions we don't want included in our analysis).
> Any advice is apprectiated.
>
> Thanks, Gerry

[Gerry]

Unfortunately, when testing it, the result came back as 0 when it should have
returned a -2.4. Could it be that I keyed something in wrong like the "--"
or should we have quotes around <0 as in "<0"? Any trouble shooting ideas?
Thanks.

[Domenic]

Did you add the sheet name for each of the references?

In article <42FB935A-80CA-4A80...@microsoft.com>,

[Gerry]

Domenic -

You're right on. Thanks. It now works. However, how does it work? Again,
thanks.

Gerry

[Domenic]

Let's assume that A1:B6 contains your data, and that the filtered data
is as follows...

Row 1 Label1 Label2
Row 2 x -20
Row 4 x 15
Row 6 x -10

If we have the following formula...

=SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B6,ROW(B2:B6)-ROW(B2),0,1)),--(B2:B6<0),
B2:B6)

SUBTOTAL(3,OFFSET(B2:B6,ROW(B2:B6)-ROW(B2),0,1)) evaluates to:

{1;0;1;0;1}

Visible cells containing data are assigned 1 and hidden cells are
assigned 0.

--(B2:B6<0) evaluates to:

{1;0;0;1;1}

Each conditional statement is evaluated as TRUE and FALSE, which is then
coerced by the double negative '--' into its numerical equivalent of 1
and 0, respectively.

B2:B6 evaluates to:

{-20;25;15;-30;-10}

SUMPRODUCT then multiplies the evaluations...

{-20;0;0;0;-10}

...which it sums, and returns -30 as the result.

Hope this helps!

In article <533581FA-E970-4083...@microsoft.com>,

[Ryan]

I have a similar issue in which i am trying to resolve. I tried using the
same formula provided on the range of cells i would like to have added
together, but my result is #N/A.

I am simply trying to get the sum of those cells D9:D37, that are not
hidden, but cannot seem to alter the above formula enough for it to work.
Could it be that the hidden cells contain #N/A themselves, thus causing the
formula to generate the same error?

If you can help that would be great.

thanks,

[Domenic]

Try the following formula, which needs to be confirmed with
CONTROL+SHIFT+ENTER...

=SUM(IF(ISNUMBER(D9:D37),SUBTOTAL(109,OFFSET(D9:D37,ROW(D9:D37)-ROW(D9),0
,1))))

--
Domenic
Microsoft Excel MVP
www.xl-central.com
Your Quick Reference to Excel Solutions

In article <3F3220AF-FCAE-45D6...@microsoft.com>,

[stanleydgromjr]

Ryan,

See the attached workbook "SUBTOTAL - sum of items not hidden - Ryan -
sdg09.xls".

See Excel Help for "SUBTOTAL".

Use:
=SUBTOTAL(109,D9:D37)

Have a great day,
Stan

+-------------------------------------------------------------------+
|Filename: SUBTOTAL - sum of items not hidden - Ryan - sdg09.xls |
|Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=226|
+-------------------------------------------------------------------+

--
stanleydgromjr
------------------------------------------------------------------------
stanleydgromjr's Profile: http://www.thecodecage.com/forumz/member.php?userid=503
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=126456

[T. Valko]

Depends on what version of Excel you're using and whether the rows are
hidden by using a filter or are they hidden manually.

If you're using Excel 2003 or later than you can use a SUBTOTAL formula.

If you're using Excel 2002 or earlier and the rows are hidden manually then
you'll need either a macro or a VBA user defined function.

--
Biff
Microsoft Excel MVP

"Ryan" <Ry...@discussions.microsoft.com> wrote in message
news:3F3220AF-FCAE-45D6...@microsoft.com...

[Ryan]

This formula worked perfectly, i will also test out the standard subtotal
formula, but i was under the assumption that it would not work if cells
contained #N/A. Oh, I am using Excel 2007.

thanks for all your help,

Ryan

[Slapukas]

Hi!

How can I exclude values hidden by the filter? My original formula is
=SUMIF($H$9:$H$140,"Planned Saving",I$9:I$140)

Your help would be very appreciated! :)

[Ritx]

Domenic,

This is excellent. Thanks a lot for this solution.

Thanks