Formula

[BeveyG]

Good morning,

{=SUM(IF($L$19:$L$148<0,$L$19:$L$148,0))}

Can someone help me troubleshoot the above formula. It is used on a
worksheet in a multi-function workbook (created by someone else - who is no
longer with the company and I can not ascertain their goal). The above
formula is used in a Trend Log for Change Orders, Pending Issues, etc. which
tracks debits and credits for each in one column. The above formula appears
in the cell just as it is shown above?????

Help! I have beaten my brain to death....

Thanks.

BeveyG

"In Memory of those who lost their lives - may we someday have Peace on
Earth and Good Will Toward All Men."

 

[Gary''s Student]

It appears that the formula sums those cells in a portion of the column that
are negative.

 

[Mike H]

Hi,

It sums the negative values in the range in the formula. to enter it you
must enter this

=SUM(IF($L$19:$L$148<0,$L$19:$L$148,0))

and then press CTRL+Shift+Enter and Excel will piut the curly brackets {}
around the formula. You can't type these yourself.

Mike

 

[BeveyG]

Hi Gary's Student,

That is correct, how can I fix it so it does not produce an error every
month? I am perplexed because other columns in the spreadsheet have negative
numbers and positive numbers as well, and the formula does not receive an
error in those columns???????????????????

OK, I'm now going bald from pulling my hair out... ) JK

Thanks for your response.
Bevey G
"In Memory of those who lost their lives on this day 7 years ago - may we
someday have Peace on Earth and Good Will Toward All Men."

 

[Gord Dibben]

The surrounding { } indicate it is an array formula entered with CTRL +
SHIFT + ENTER

What would you like to troubleshoot?


Gord Dibben MS Excel MVP

 

[BeveyG]

Hi Mike,

Thank you for your response. I did as suggested but I am still receiving a
#VALUE!error. Could this be caused by a cell that has a calculation in it
2,265-1043.6 and the cell value is 1221.40 could this effect the formula.

I so appreciate your help Mike.

Thanks.

BeveyG

 

[Dave Peterson]

If you don't have any errors in that range, then:

=SUMIF($L$19:$L$148,"<"&0)
or
=SUMIF($L$19:$L$148,"<0")


should work ok.

(this is not an array formula.)

 

[BeveyG]

Hi Gord,

Can you please look at the message above from me responding to Mike? I am
really perplexed by this and why I am getting the error when all other
columns in the spread sheet have the same values both negative and positive.

Thanks so much,

BeveyG

 

[Gord Dibben]

You have several other replies addressing this problem.

Have a look at them.


Gord