Advanced Sumif formula

[Guest]

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Guest]

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

 

[Rick Rothstein \(MVP - VB\)]

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

Rick

 

[Guest]

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

 

[Guest]

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")

 

[Guest]

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

 

[Guest]

Not really, but I learned from the best!!

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