Can you help me with the following sytuation


I have data in sheet1 in a vertical order

Sheet1
A B C D
data Info1 Info2 Info3
information 55 5 -1
information -5 -9 2

In sheet2 I need to sum values that are less than 0
Sheet2
Info1 =SUMIF(B3:B5,"<0",B3:B5)
Info2 =SUMIF(c3:c5,"<0",c3:c5)
Info3

The problem is that I don't want to change manually range of each one
category
I need a range to be more flexable.

Is it possible to do it?
Thanks
Eva

> I have data in sheet1 in a vertical order
>
> Sheet1
> A B C D
> data Info1 Info2 Info3
> information 55 5 -1
> information -5 -9 2
>
> In sheet2 I need to sum values that are less than 0
> Sheet2
> Info1 =SUMIF(B3:B5,"<0",B3:B5)
> Info2 =SUMIF(c3:c5,"<0",c3:c5)
> Info3
>
> The problem is that I don't want to change manually range of each one
> category
> I need a range to be more flexable.

Try these on your Sheet2 (looking at values on Sheet1)...

Info1: =SUMPRODUCT((Sheet1!B2:B1000<0)*Sheet1!B2:B1000)

Info2: =SUMPRODUCT((Sheet1!C2:C1000<0)*Sheet1!C2:C1000)

Info3: etc.

Note, I use 1000 as a maximum row, but you can change them to anything equal
to or larger than the largest row you expect to fill data into.

Rick

Yes that's possible.

Enter you first formula manually:-

=SUMIF(B3:B5,"<0",B3:B5)

Then drag the formula to the right abd B will change to C etc.

If you then want your answers in a column instead of a row, copy the snawers
then Paste special and check Transpose.

Mike
"Eva" wrote:

> Can you help me with the following sytuation
>
>
> I have data in sheet1 in a vertical order
>
> Sheet1
> A B C D
> data Info1 Info2 Info3
> information 55 5 -1
> information -5 -9 2
>
> In sheet2 I need to sum values that are less than 0
> Sheet2
> Info1 =SUMIF(B3:B5,"<0",B3:B5)
> Info2 =SUMIF(c3:c5,"<0",c3:c5)
> Info3
>
> The problem is that I don't want to change manually range of each one
> category
> I need a range to be more flexable.
>
> Is it possible to do it?
> Thanks
> Eva

After reading Mike's post, I think I completely misread your question.
Sorry.

Rick


"Rick Rothstein (MVP - VB)" <(E-Mail Removed)> wrote in
message news:(E-Mail Removed)...
>> I have data in sheet1 in a vertical order
>>
>> Sheet1
>> A B C D
>> data Info1 Info2 Info3
>> information 55 5 -1
>> information -5 -9 2
>>
>> In sheet2 I need to sum values that are less than 0
>> Sheet2
>> Info1 =SUMIF(B3:B5,"<0",B3:B5)
>> Info2 =SUMIF(c3:c5,"<0",c3:c5)
>> Info3
>>
>> The problem is that I don't want to change manually range of each one
>> category
>> I need a range to be more flexable.
>
> Try these on your Sheet2 (looking at values on Sheet1)...
>
> Info1: =SUMPRODUCT((Sheet1!B2:B1000<0)*Sheet1!B2:B1000)
>
> Info2: =SUMPRODUCT((Sheet1!C2:C1000<0)*Sheet1!C2:C1000)
>
> Info3: etc.
>
> Note, I use 1000 as a maximum row, but you can change them to anything
> equal to or larger than the largest row you expect to fill data into.
>
> Rick

Thank you Rick. It works, except that when I copy it down the column number
don't change...Any solution?

One more thing but I don't think that it can be resolved. For me the info is
important, so the best thing is if the formula recognize both data (info1 in
sheet1 and info1 in sheet2= >match it) and then total the numbers that are
less than 0.
I tried many tricks I know and nothing seems working.
I really appreciate if you can help me.
Thanks
Eva

"Rick Rothstein (MVP - VB)" wrote:

> > I have data in sheet1 in a vertical order
> >
> > Sheet1
> > A B C D
> > data Info1 Info2 Info3
> > information 55 5 -1
> > information -5 -9 2
> >
> > In sheet2 I need to sum values that are less than 0
> > Sheet2
> > Info1 =SUMIF(B3:B5,"<0",B3:B5)
> > Info2 =SUMIF(c3:c5,"<0",c3:c5)
> > Info3
> >
> > The problem is that I don't want to change manually range of each one
> > category
> > I need a range to be more flexable.
>
> Try these on your Sheet2 (looking at values on Sheet1)...
>
> Info1: =SUMPRODUCT((Sheet1!B2:B1000<0)*Sheet1!B2:B1000)
>
> Info2: =SUMPRODUCT((Sheet1!C2:C1000<0)*Sheet1!C2:C1000)
>
> Info3: etc.
>
> Note, I use 1000 as a maximum row, but you can change them to anything equal
> to or larger than the largest row you expect to fill data into.
>
> Rick
>
>

Where your table is:
A B C D
1 data Info1 Info2 Info3
2 information 55 5 -1
3 information -5 -9 2

and on sheet2 you have "Info1" in cell A1 (Info2 in A2, etc), try this in B1
and copy down:

=SUMIF(INDEX(Sheet1!$B$2:$D$3,,MATCH(A1,Sheet1!$B$1:$D$1,0)),"<0")



"Eva" wrote:

> Can you help me with the following sytuation
>
>
> I have data in sheet1 in a vertical order
>
> Sheet1
> A B C D
> data Info1 Info2 Info3
> information 55 5 -1
> information -5 -9 2
>
> In sheet2 I need to sum values that are less than 0
> Sheet2
> Info1 =SUMIF(B3:B5,"<0",B3:B5)
> Info2 =SUMIF(c3:c5,"<0",c3:c5)
> Info3
>
> The problem is that I don't want to change manually range of each one
> category
> I need a range to be more flexable.
>
> Is it possible to do it?
> Thanks
> Eva

Works just perfect!!!! Thank you very much. You are the best!
Eva

"JMB" wrote:

> Where your table is:
> A B C D
> 1 data Info1 Info2 Info3
> 2 information 55 5 -1
> 3 information -5 -9 2
>
> and on sheet2 you have "Info1" in cell A1 (Info2 in A2, etc), try this in B1
> and copy down:
>
> =SUMIF(INDEX(Sheet1!$B$2:$D$3,,MATCH(A1,Sheet1!$B$1:$D$1,0)),"<0")
>
>
>
> "Eva" wrote:
>
> > Can you help me with the following sytuation
> >
> >
> > I have data in sheet1 in a vertical order
> >
> > Sheet1
> > A B C D
> > data Info1 Info2 Info3
> > information 55 5 -1
> > information -5 -9 2
> >
> > In sheet2 I need to sum values that are less than 0
> > Sheet2
> > Info1 =SUMIF(B3:B5,"<0",B3:B5)
> > Info2 =SUMIF(c3:c5,"<0",c3:c5)
> > Info3
> >
> > The problem is that I don't want to change manually range of each one
> > category
> > I need a range to be more flexable.
> >
> > Is it possible to do it?
> > Thanks
> > Eva

Not really, but I learned from the best!!

"Eva" wrote:

> Works just perfect!!!! Thank you very much. You are the best!
> Eva
>
> "JMB" wrote:
>
> > Where your table is:
> > A B C D
> > 1 data Info1 Info2 Info3
> > 2 information 55 5 -1
> > 3 information -5 -9 2
> >
> > and on sheet2 you have "Info1" in cell A1 (Info2 in A2, etc), try this in B1
> > and copy down:
> >
> > =SUMIF(INDEX(Sheet1!$B$2:$D$3,,MATCH(A1,Sheet1!$B$1:$D$1,0)),"<0")
> >
> >
> >
> > "Eva" wrote:
> >
> > > Can you help me with the following sytuation
> > >
> > >
> > > I have data in sheet1 in a vertical order
> > >
> > > Sheet1
> > > A B C D
> > > data Info1 Info2 Info3
> > > information 55 5 -1
> > > information -5 -9 2
> > >
> > > In sheet2 I need to sum values that are less than 0
> > > Sheet2
> > > Info1 =SUMIF(B3:B5,"<0",B3:B5)
> > > Info2 =SUMIF(c3:c5,"<0",c3:c5)
> > > Info3
> > >
> > > The problem is that I don't want to change manually range of each one
> > > category
> > > I need a range to be more flexable.
> > >
> > > Is it possible to do it?
> > > Thanks
> > > Eva