Google Sheets – Operations on Hyperlink Labels of Cells in Google Sheets

google sheets

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?

Best Answer

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

Related Topic