Is there a formula that allows me to sum the values in a variable Range,
whereby a fixed amount is subtracted from each value in the Range?

e.g. Column A contains the following data:

A1 Amount
A2 10
A3 -5

The value of cell B1 has to be subtracted from each value in the A
range.
B1 contains 1.

So the expected value should be: 9 + (-6) = 3

Using the (array) formula
-{=SUM(OFFSET($A$2,0,0,COUNTA($A:$A)-1,1)-$B$1)} - produces the
unexpected answer -2! The non-array version produces a #VALUE error.


--
rvExcelNewTip
------------------------------------------------------------------------
rvExcelNewTip's Profile: http://www.excelforum.com/member.php...o&userid=15668
View this thread: http://www.excelforum.com/showthread...hreadid=376699