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

  • Thread starter Thread starter Guest
  • Start date Start date
G

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

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

Hope this helps!
 
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 -

You're right on. Thanks. It now works. However, how does it work? Again,
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!
 
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,
 
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))))
 
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.
 
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
 
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! :)
 
Back
Top