This is quite a natural problem, but I couldn't think of an easy solution without creating some extra columns. I suggest a custom function such as partialTotal below. Its parameters are:
- The range of data to process
- The number of items you want to add
- The number of the column containing counts (relative to the range)
- The number of the column containing quantity to be totaled (relative to the range)
- Whether to take the latest data (bottom rows): true or false.
Example: suppose you have the following in the columns B:E, and you want to total the sales for the last 8 items of type "want".
+-------+-------+-------+------+
| Item | Price | Count | Sale |
+-------+-------+-------+------+
| stuff | 9 | 8 | 11 |
| want | 8 | 7 | 22 |
| want | 9 | 6 | 33 |
| stuff | 8 | 5 | 44 |
| want | 9 | 4 | 55 |
| stuff | 8 | 3 | 66 |
+-------+-------+-------+------+
The command would be
=partialTotal(filter(B2:E, B2:B="want"), 8, 3, 4, true)
where filtering happens first, and then totaling: take 8 items, counts are in the 3rd column (within the range), quantities to total are in the 4th column, and latest are wanted. The output is 77, which is 55 + 33*(4/6).
Here is the code of the custom function, to be placed in Tools > Script Editor.
function partialTotal(data,count,countColumn,quantityColumn,latest) {
if (latest) {
data.reverse();
}
var total = 0;
var i = 0;
while (count>0 && i<data.length) {
if (data[i][countColumn-1] <= count) {
total = total + data[i][quantityColumn-1];
count = count - data[i][countColumn-1];
}
else {
total = total + data[i][quantityColumn-1]*count/data[i][countColumn-1];
count = 0;
}
i++;
}
return total;
}
Best Answer
You're going to want to use the function
=sumproduct()In your case:
=sumproduct(B3:E18,I3:L18)Enter that in an empty cell to the side and your result will appear there when you enter values into the cells on the righthand table.