Google Sheets – Using SUM() and VALUE() Functions

google sheets

I have a spreadsheet with invoice amounts. Each amount is a link to the given invoice. Can I use sum() and value() (or any other functions) to somehow use the displayed value for sum() (and not the actual hyperlink)?

Update: Sample Spreadsheet

Best Answer

Yes, but you have to use an Array Formula, like this:

=ArrayFormula(SUM(VALUE(A1:A7)))

I took the liberty to edit your Sample spreadsheet.

You can write

=SUM(VALUE(A1:A7))

and press ctrl + shft + enter to make it an Array formula.

About array formulas (from here):

"Single-cell" array formulas let you write formulas with array inputs, instead of array outputs. When you wrap a formula inside an =ARRAYFORMULA function, you can pass arrays and ranges to functions and operators that usually only take non-array arguments. Those functions and operators will apply to each entry in the arrays one at a time, and return a new array with all of the outputs.

It basically runs a function which only takes one cell (in this case the value-function) and runs it on every cell in the range.

Related Topic