Advanced Sumif formula
 

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

Advanced Sumif formula
 

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

Advanced Sumif formula
 

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

Advanced Sumif formula
 

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

Rick


"Rick Rothstein (MVP - VB)" wrote in
message ...
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

Advanced Sumif formula
 

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

Advanced Sumif formula
 

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

Advanced Sumif formula
 

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

Advanced Sumif formula
 

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