3

I am using hyperlinks on some price values on cell in Google Docs. So my cells look like this:

=HYPERLINK("http://www.site.com/item","$25.00")

I am trying to perform a SUM operation on a bunch of these cells but it was always returning 0. After investigating a little, I realized that it was because the value of the cell is not in fact the amount, but the hyperlink function. Is there a way to accomplish what I want while keeping the hyperlinks and the price in the same cell?

4

You can sum the values used in the HYPERLINK formula.

Formula

=SUM(ARRAYFORMULA(VALUE(RIGHT(A1:A5, LEN(A1:A5)-1))))

Explained

The LEN formula determines the length of the string. The RIGHT formula allows you to extract characters from a string from the right side on. It uses the length of the string minus 1 (the $). After that, the returned text in forced to be used as a value, through the VALUE formula. An ARRAYFORMULA makes it possible to use a range and the SUM formula performs the mathematical summation.

Screenshot

hyperlink
enter image description here

sum
enter image description here

Example

I've created an example file for you: Sum values used in hyperlinks

1

You cannot sum the contents of cells containing hyperlinks, however, you can make your hyperlinks retrieve data from other cells. If you don't want to see these other cells, just hide the corresponding columns. For instance:

|   A   |   B   |         C          |
--------------------------------------
| http:…|$25.00 | =hyperlink(A:A,B:B)|

You can now sum elements of column B as you like, and if you do not want to see columns A and B in your spreadsheet, just hide them.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.