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

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