Google-sheets – How to find a string then find the sum of all cells next to the found string

I wasn't able to reproduce your results. As a matter of fact, it worked perfectly.

What you tried to do is most probably the following:

In A1 you typed in =HYPERLINK(CONCATENATE("http://www.example.com/id/",A1);A1) and this yields an error of coarse.

Update

If you really want to get the result in A1, then you need to use a script.

Code

// global
var ss = SpreadsheetApp.getActiveSpreadsheet();

function onOpen() {
  var menu = [{name: "create URL", functionName: "createURL"}];
  ss.addMenu("URL", menu);
}

function onEdit(e) {
  var activeRange = e.source.getActiveRange();

  if(activeRange.getColumn() == 1) { 
    if(e.value != "") { 
      activeRange.setValue('=HYPERLINK("http://www.example.com/id/'+e.value+'","'+e.value+'")');
    }
  }
}

function createURL() {
  var aCell = ss.getActiveCell(), value = aCell.getValue();
  aCell.setValue('=HYPERLINK("http://www.example.com/id/'+value+'","'+value+'")');  
}

Explained:

The e.value will retrieve the cells value (only applicable a cell). The setValue() will add the concatenated string into the getActiveRange(). All is only executed when e.value contains something and the active range is in column A.

I've created an extra menu option as well, to be able access the script this way.

Example:

I've created an example file for you: onEdit URL builder
Add this script via Tools>Script editor, into the script editor. Press the "bug" button and you can use the script.

If the data you want to use is in, say, column E (5th column), then instead of

sheet.getRange(row,3).setValue(1);

you would have

sheet.getRange(row,3).setValue(sheet.getRange(row,5).getValue() + 4);

Or, if the computation involves several values in that row, say in the first ten columns, then get the row all in once:

var values =  sheet.getRange(row, 1, 1, 10).getValues(); 
var output = values[0][4] + 2*values[0][7] - 3*values[0][9];
sheet.getRange(row, 3).setValue(output);

In the latter version, JavaScript array indexing is used, where indices begin at 0, unlike in Sheets. So, values[0][4] means the zeroth row, fourth column in the fetched range; in practice, column E of the row that was fetched.

Also, the approach with getLastRow() may skip some entry if two arrive simultaneously. If you want to guard against that, get all the data and process any rows that aren't yet. Like this:

var values =  sheet.getDataRange().getValues(); 
for (var i=0; i<values.length; i++) {
  if (values[0][2] === "") { 
    var output = values[0][4] + 2*values[0][7] - 3*values[0][9];
    sheet.getRange(i+1, 3).setValue(output);
  }
}