# How can I sum the labels of a range of hyperlink functions whose link_labels are numeric/prices?

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?

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.

sum

## Example

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

• Very, very neat trick! Commented Jun 17, 2015 at 18:12
• As of Jun 30, 2024, this is too convoluted. Nowadays, the VALUE function does the same job. Commented Jul 1 at 2:16
• Or use the SUMPRODUCT function Commented Jul 1 at 3:02

Use numeric labels, like this:

``````=hyperlink("http://www.site.com/item", 25.00)
``````

Format the cell as Format > Number > Currency. The hyperlinked cells will contain numbers that can be directly aggregated with functions like `sum()`, `min()` and `max()`.

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          |
--------------------------------------
``````

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.

• This is not true as of June 30, 2024. The VALUE function is used to get the value (number) from hyperlink text. Commented Jul 1 at 2:18

As of June 30, 2024, use the VALUE Google Sheets built-in function.

Showing Formulas

A B
1 `=HYPERLINK("https://www.example.com", "\$25.00")` `=VALUE(A1)`

Showing Result of Formulas

A B
1 \$25.00 25

The value shown in B1 is a number.

I am trying to perform a SUM operation on a bunch of these cells

If the HYPERLINK labels are text-formatted numbers, you can use them in calculations without the need to first use additional functions. Sheets will coerce them to numbers on your behalf.

In the case of an array or range of hyperlinks you need to coerce them to numbers which can be aporoached as Rubén and Jacob suggest using the VALUE function:

``````=SUM(INDEX(VALUE(A:A)))
``````

Or alternatively by applying a calculation to the array/range:

``````=SUM(INDEX(A:A*1))
``````

Or using SUMPRODUCT to achieve the same thing:

``````=SUMPRODUCT(A:A)
``````