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

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

Try...

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

Hope this helps!

In article ,
"Gerry" 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

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" wrote:

Try...

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

Hope this helps!

In article ,
"Gerry" 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

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

In article ,
"Gerry" wrote:

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" wrote:

Try...

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

Hope this helps!

In article ,
"Gerry" 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

Domenic -

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

Gerry

"Domenic" wrote:

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

In article ,
"Gerry" wrote:

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" wrote:

Try...

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

Hope this helps!

In article ,
"Gerry" 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

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 ,
"Gerry" wrote:

Domenic -

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

Gerry

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

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,

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

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

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

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

In article ,
Ryan wrote:

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,

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

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/sh...d.php?t=126456

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

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

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

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

"Domenic" wrote:

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

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

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

In article ,
Ryan wrote:

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,

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

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! :)

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

Domenic,

This is excellent. Thanks a lot for this solution.

Thanks


"Domenic" wrote:

Try...

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

Hope this helps!

In article ,
"Gerry" 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