sum current values

[simpson]

I have a column that creats data based on formulas. I would like to sum the column at the bottem but I get a circuler ref. error. I think this is because of the formulas but is there a way to sum based on the actual values in the column?

[sweep]

The circular reference isn't because of the formulas. You're referring to the cell showing the error in the cell containing the error.

For example,

in A10: =SUM(A1:A100)

will produce a circular reference error

[darkyam]

If you want it to only sum certain data based on that data, you should be able to use SUMIF, SUMIFS, or SUMPRODUCT. What exactly are you trying to do?

[simpson]

one of the formulas we created puts a 0 on the cell if none of the other ref. info. gets filled in. I wasnt to sum the column that contains all those values but get a circuler ref. I thought it was because the 0's dont represent real values only the absense of ref. info.
If the values in a colum are created through a formula and 0 is imput as an error value how do sum the column's text numbers?

[darkyam]

Methinks a sample workbook would help. I don't understand what you mean by "text numbers". Numbers should not, generally speaking, be stored as text or inputted in formulas using quotes. There are exceptions, but doing so often causes problems such as not being able to add the numbers later.

As sweep said, a circular reference occurs when a formula either refers to itself or when two (or more) cells have formulas that refer to each other. It may not be an error with how your formula is set up so much as an error in the range you're using in the formulas.

[simpson]

Here is the formula i am using in the cells I want to add (column W). It puts the higher of the two numbers in the cell and puts 0 in the other cell based on the repeat in column D. but if there is no repeat, or number, in D then 0 is also put in W. I want to sum W.
Sory no sample. dont kow how to create it.....

=IF(MAX(IF(D6=D7,W7,0),V6,IF(D6=D7,W7,0))=V6,V6,0)

[darkyam]

You list IF(D6=D7,W7,0) twice.
We can't really help without seeing the workbook. Can you please scrub all confidential data and post a workbook with a couple lines of fake data, along with expected results, like you did in your last thread?

[simpson]

comin right up.

[simpson]

sum column W at the bottem of the column. data on page 30.

[darkyam]

I'm missing something. Why not use =SUM(W6:W36)? Also, where is the cell that has the circular reference?

[simpson]

did you try it? I tried that exact formula and get the circular ref. error. It shows a blue dotted line in W15-W35.

[darkyam]

Yes, and it works fine for me.

[simpson]

somethings wrong here. mine shows a circular ref. at W15 to W 36

[simpson]

Got it! Must have been a formula error. ???