Google-sheets – How to anchor all the cells on the page in Google Spreadsheet

Ok - this isn't simple but it can be done. There may be a more elegant way but here's a way that will work, using only the terms you are providing.

Take a range of cells as follows

Col  A   B       C
2    1   300     2
3    2   450     4
4    3   700    
5    4   900    
6    5   1000   
7    6   1200   

Take these formulas:

Cell A10: 
=sumif(A2:A7,"<="&C2,B2:B7)

Cell A11:
=sumif(A2:A7,">="&C3,B2:B7)

Cell B10:
=sum(B2:B7)

Cell A12:
=(A10+A11-B10)*-1

Cell A12 will have the results you want. Hopefully you get the idea.

Basically you sum all the data elements you don't want in your range, subtract that from the total sum of the range and the remainder is the sum of the range you want.

I didn't do much bug checking for corner cases, but hopefully this will give you enough of a start to solve the problem?

It will depend upon what else you have in your spreadsheet. Text like "Minutes(Cardinal)" might require care. First I would have the formulae visible (View, All formulae or Ctrl+`).

Then it rather depends upon the formulae, of which you have only provided a single example. A column as the example that had been copied down could be changed with simple Edit, Find and Replace.... of (C with ($C and ;C with ;$C, with Also search within formulae.

With two columns the same (say Cs and also Ds), Search using regular expressions could be applied to save repeating the above for Ds, with finding:

(\()(.) and  
(\;)(.)  

and in both cases replacing with:

$1$ $2  

Two columns for example, but it could be the whole sheet. Unfortuantely that regex introduces spaces into the formulae, though those can then be removed quite easily and in bulk.

So possible, but a peculiar requirement for which they may be a better alternative solution.