|
|
|
|
|
|
|
|
|
|
| |
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:EH2001)-ROW(EH5),0,1)),-
> -(EH5:EH2001<0),EH5:EH2001)
>
> Hope this helps!
>
> In article <E134A2C3-D8A8-4BD9-B71A-2D82649C63F5@microsoft.com>,
> "Gerry" <Gerry@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
> |
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
 Reply |
| |
 |
|
 Top |
|
|
|
|
|
|
|
|
|
| |
Did you add the sheet name for each of the references?
In article <42FB935A-80CA-4A80-8EE9-65B7F001423F@microsoft.com>,
"Gerry" <Gerry@discussions.microsoft.com> 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:EH2001)-ROW(EH5),0,1)),-
> > -(EH5:EH2001<0),EH5:EH2001)
> >
> > Hope this helps!
> >
> > In article <E134A2C3-D8A8-4BD9-B71A-2D82649C63F5@microsoft.com>,
> > "Gerry" <Gerry@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
> >
|
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
|
|
| |
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 <42FB935A-80CA-4A80-8EE9-65B7F001423F@microsoft.com>,
> "Gerry" <Gerry@discussions.microsoft.com> 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:EH2001)-ROW(EH5),0,1)),-
> > > -(EH5:EH2001<0),EH5:EH2001)
> > >
> > > Hope this helps!
> > >
> > > In article <E134A2C3-D8A8-4BD9-B71A-2D82649C63F5@microsoft.com>,
> > > "Gerry" <Gerry@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
> > >
> |
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
| |
 Answer |
|
| |
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-945C-CA246FB4511A@microsoft.com>,
"Gerry" <Gerry@discussions.microsoft.com> wrote:
> Domenic -
>
> You're right on. Thanks. It now works. However, how does it work? Again,
> thanks.
>
> Gerry
|
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
|
|
|
|
|
| |
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-9A39-E93CF5638DA4@microsoft.com>,
Ryan <Ryan@discussions.microsoft.com> 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,
|
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
|
|
| |
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: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-9A39-E93CF5638DA4@microsoft.com>,
> Ryan <Ryan@discussions.microsoft.com> 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,
> |
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| |
Domenic,
This is excellent. Thanks a lot for this solution.
Thanks
"Domenic" wrote:
> 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-B71A-2D82649C63F5@microsoft.com>,
> "Gerry" <Gerry@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
> |
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
Question
Notify me of replies