Sum A Varying Name

[elotromateo]

Currently, I have data in column A that is referred to as the name, x (x=Sheet1!$A1). y = x^2. Column B is populated with y, so the spreadsheet looks like this.

	A	B
1	5	25
2	6	36
3	7	49
4	8	64

In another part of the sheet I sum column B and and get 174. What I would like to do is get rid of column B and go straight to the single cell with a sum. Is there a way to do this without using arrays?

(For simplicity's sake, these are neither the actual formulas or columns)

 

[tigeravatar]

elotromateo,

You could use a sumproduct that has the column A range twice:
=SUMPRODUCT(A1:A4,A1:A4)

Hope that helps,
~tigeravatar

 

[elotromateo]

Sorry, I should have been more obvious, these are not the actual formulas or columns. There are many columns (which is why I am looking to reduce) and the formulas are a rather long, layered if statements, but the same theory should hold since I am trying to sum a name that varies by location.

 

[Dan Waterloo]

I think the purpose of array formulas is to do exactly the sort of thing that you are asking for here. Why would you want to avoid using array formulas?

 

[elotromateo]

There is a UDF that is part of the formula that does not seem to like arrays. It isn't mine and my adventures in creating and modifying functions tend to end in disaster.