SUMIF type of calc not working

Posted by Heidi on February 11, 2002 11:52 AM

Hi there Here is the deal:
I have a column of figures (column a) and next to it in column b i want to show the sum of each figure in column a + a constant value referenced in another cell, But, i only want it to add that constant if the number in column a is greater than zero. If the number is 0 than i just want the number zero to show in the column b, for that record. Can this be done? I tried SUMIF and it appears to only work with 2 ranges, not a constant in another cell. Do I have to insert a third column with the constant?? Hopefully not, thanks for you help:)

Posted by IML on February 11, 2002 11:58 AM

I'm guessing either
=(A1+$E$1)*(A1 > 0)
or
=MAX(A1+$E$1,0)
may meet your needs, where E1 is your constant.
the first will return zero if a1 is negative
the second will return zero if a1 plus e1 is negative.

Good luck

Posted by Aladin Akyurek on February 11, 2002 12:01 PM

Heidi --

It's unclear to me what you are summing in A. Care to elaborate?

Aladin

==========

Posted by Heidi on February 11, 2002 12:10 PM

In column there is just one constant that only needs to be added to the other constant if it is> 0: I tried =sumif(a12:a12,">0")sum(a12+D$4$)
but got an error. I only used a range because the function builder asked for a range. Really it only needs to be a12 itself compared to the >0 argument and than added to the constant $d$4

Does that make more sense?

Posted by Heidi on February 11, 2002 12:17 PM

Thanks the first one worked perfectly:) Have a nice day..

Posted by Aladin Akyurek on February 11, 2002 12:24 PM

Yep. And, I believe, a friend already posted the solution.

PS. I advise my students not to use those wizards, especially the paperclip...

===========

Posted by Heidi on February 11, 2002 12:44 PM

Yes I am learning about the evils of Mr. Clippy;)
Here is one more question. The following formula is working except for when a value in c12:c202 is equal to zero. Since zero is less than the value in B3, counting those zero records messes up my average. I only want to count it if it is less than B3 and &LT;&GT; to zero. Can it be done?
=COUNTIF(C12:C202,"&LT;"&$B$3)/B11*(0.01)

ps- Where are you, in the world? Just curious. This board is the coolest thing I've ever used on the net. Yes, even cooler than web van used to be:)

Posted by Aladin Akyurek on February 11, 2002 1:13 PM

> Yes I am learning about the evils of Mr. Clippy;)
> Here is one more question. The following formula is working except for when a value in c12:c202 is equal to zero. Since zero is less than the value in B3, counting those zero records messes up my average. I only want to count it if it is less than B3 and &LT;&GT; to zero. Can it be done?
> =COUNTIF(C12:C202,"&LT;"&$B$3)/B11*(0.01)

=(COUNTIF(C12:C202,"&LT;"&$B$3)-COUNTIF(C12:C202,0))/B11*(0.01)

A tiny part of the Old Continent, called The Netherlands.

================