The script below will perform a summation throughout tabs, taking into account different strings, as shown in your question.

## Code

```
function mySum(tabRange, strings) {
// small error handling
if(typeof tabRange === 'string') {
throw 'Multiple tabs are needed. Otherwise use SUM';
return
}
// get active spreasdsheet and create empty arrays
var ss = SpreadsheetApp.getActive(), aStr = [], output = [];
// push first result in array to control different strings
aStr.push(strings[0][0], 0);
// iterate through tabs
for(var i = 0, iLen = tabRange.length; i < iLen; i++) {
// get all data (at once) from tab
var data = ss.getSheetByName(tabRange[i][0]).getDataRange().getValues();
// iterate through strings
for(var k = 0, kLen = strings.length; k < kLen; k++) {
// create and set the summation per string
var strSum = 0;
// iterate through the data
for(var j = 0, jLen = data.length; j < jLen; j++) {
// if a match is found, then add to the strSum
if(data[j][1] === strings[k][0]) {
strSum += data[j][0];
}
}
// find index in array that matches a string
var index = aStr.indexOf(strings[k][0]);
// if found, then add to existing value
// else, create a new entry for that string
// so that it will be found the next time
if(index !== -1) {
aStr[index+1] += strSum;
} else {
aStr.push(strings[k][0], strSum);
}
}
}
// get rid off the strings
for(var l = 1, lLen = aStr.length; l < lLen; l += 2) {
output.push(aStr[l]);
}
// show the outcome
return output;
}
```

## Screenshot

## Example

I've created an example file for you: **Sum of dynamic number of values derived from fomula**

This can be done by using the running total as an intermediate result (you can put in some column further on the right and/or make it hidden, if you don't want to see it).

I'll use column C for the cumulative total and column D for 7-day total; finding the average is simple division.

The formula for cumulative total is found here. I'll use it in this form

```
C2 =ARRAYFORMULA(IF(LEN(A2:A), SUMIF(ROW(B2:B),"<="&ROW(B2:B), B2:B), ""))
```

The conditional statement about `LEN(A2:A)`

prevents output in rows where no data is present.

And here is the computation of 7-day running total:

```
D2 =ARRAYFORMULA(IF(LEN(A2:A), C2:C-IFERROR(VLOOKUP(A2:A-7, A2:C, 3), 0), ""))
```

The key player here is the `VLOOKUP`

function which find the latest date that is less than or equal to "a week ago", and returns the cumulative total for it. This represents the total of "old" entries, which should be subtracted from the current total so that only the entries from the last 7 days are counted.

The `IFERROR`

statement handles the case when there is nothing to subtract, i.e., all preceding dates are within the 7-day window.

Here is sample output to verify correctness:

```
+-----------+------+---------------+-------------+
| Date | Data | Running Total | 7-day total |
+-----------+------+---------------+-------------+
| 1/4/2015 | 5 | 5 | 5 |
| 1/5/2015 | 3 | 8 | 8 |
| 1/9/2015 | 1 | 9 | 9 |
| 1/11/2015 | 9 | 18 | 13 |
| 1/12/2015 | 4 | 22 | 14 |
| 1/13/2015 | 2 | 24 | 16 |
| 1/27/2015 | 64 | 88 | 64 |
+-----------+------+---------------+-------------+
```

## Best Answer

Use the following formula to accomplish that:

The

`range`

is of course the one you set it to be !!EDIT 29-03-2013; 23:00 CESTIMPORTANT:This is only valid if you work at least 70 hours per week, each week. It is just as important to know if you have worked less !! Therefore you need to change the way you calculate the "overtime". The following formula will show only a calculated value, if a "hours worked" value has been entered:`=IF(B2="";"";B2-70)`

See example file, column E.

EDIT 29-03-2013; 22:30 CESTI've created an example file: Excluding Negative Values. Here I tried to clarify the problem, the way the OP sees it. In doing so, I incorporated the other answers as well.

The solution given by SpreadsheetPro.net works perfectly. The statement about the

`SUMIF(S)`

not being available in Google Spreadsheet, is false.The solution handed by Al, covers a different approach. Instead of calculating

`Hours Worked MINUS Hours Working Week`

, Al is telling the formula to return`ZERO`

is value is below`ZERO`

, see column D in example.