syntax for adding up different cols

[cmeredith1973]

Hi - I am trying to have an if(or(and syntax) and am trying to solve this true or false statement. I wanted to ask for the forum for some help, thank you in advance.

I have 2 col's, B6 which either equals 0, 1 or more than 1.
Col J, is has values going from 0-100% or more.

=(IF(B6=1,SUM(J:J),IF(AND(B6>1,SUM(J:J>95%)),"total over qty",SUM(J:J))))

I wanted to ask how I can get the syntax to flow. It works but never equals the true statement of "total over qty", it always sums the final j col.

What I am trying to is measure a table of products. when theres 1 in b6, i just want it to sum col J. when its more than 1, i want to to say "full" when col J is 95% or higher. When its less than 95%, i want it to sum col J.

 

[Coding4Fun]

I am not sure I fully understand but will try to help.

From what I interpret from your question your formula appears to work as intended.

I created a sheet and in Column B I entered either 0, 1 or 3
I also populated column J with the values in the range of 0-100%

I then inserted your formula into column K.

Column B	Column J	Column K (Formula) =(IF(B1=1,SUM(J:J),IF(AND(B1>1,SUM(J:J>95%)),"total over qty",SUM(J:J))))
0	0%	422
1	95%	422
3	97%	total over qty
3	80%	422
0	75%	422
1	75%	422

Based on this when the value of column B is over 1 (Not = 1 but > 1) AND column J is over 95 then it will show the text "total over qty"
Otherwise it will sum the entire column J which in this example is 422(0 + 95 + 97+ 80 + 75 + 75)

If you can clarify what is not working specifically I may be able to assist.

What I am trying to is measure a table of products. when theres 1 in b6, i just want it to sum col J. when its more than 1, i want to to say "full" when col J is 95% or higher. When its less than 95%, i want it to sum col J.

Based on what you posted this is doing just that, when column B = 1 it sums all of column J, when its greater than one AND column J is 95% or greater it returns "total over qty", when B is greater than 1 and Column J is less than 95% it returns the sum of column J

I did notice that it did not work properly until I had actually formatted column J as %

 

[cmeredith1973]

Hey thank you - the suggestion of the format helped. I was able to solve this way with 2 cheater sum up col's.

o1= sum J;J b6 = sum b7;b10

=IF(B6=1,O1,IF(AND(B6<>1,O1>95%),"Truck Full",O1))