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

[Guest]

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]

Try...

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

Hope this helps!

 

[Guest]

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?

 

[Guest]

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!

 

[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 D937, 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(D937),SUBTOTAL(109,OFFSET(D937,ROW(D937)-ROW(D9),0
,1))))

 

[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.

 

[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