Summing up content from hyperlink cells

[Guest]

Anyone have any idea on how I can make Excel sum up the displayed values of
several yperlink cells?

 

[Guest]

This is an EXCELLENT question !
I will use it next semester.

Let's say that in A1 thru A3 we have:

=HYPERLINK("http://www.microsoft.com","15")

each of the cells displays 15

However
=SUM(A1:A3) shows 0
This is because the 15's are actually text values. They must be converted
to numbers. Use:

=SUM(--A1:A3) which must be entered as an array formula with
CNTRL-SHIFT-ENTER rather than just ENTER

 

[Dave Peterson]

I didn't understand the original question, but why not just fix the formulas:

=HYPERLINK("http://www.microsoft.com","15")
becomes
=HYPERLINK("http://www.microsoft.com",15)

 

[Guest]

You are correct.

The only advantage to my shabby little trick is that the OP needs to update
only a single SUM formula rather than a collection (potentially large) of
HYPERLINK formulas.

 

[Dave Peterson]

It's a good bandaid <bg>. But sooner or later, there's gonna be more formulas
that refer to those cells. And someday, someone will miss the fix in the new
formula.

It always scares me when the original source isn't fixed--although, I'm sure
everyone has had to do scary things like this (when you depend on someone else
for the data, especially).