Sum of a Range (with a twist)

[rvExcelNewTip]

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.

 

[mangesh_yadav]

A simple formula:
=SUM(A1:A2)-COUNT(A1:A2)*(B1)

Mangesh

 

[Guest]

Try:

=SUM(A:A)-COUNTA(A:A)*$B$1

HTH

 

[Nick Hebb]

Your formula is basically of the format:
(A-X) + (B-X) + (C-X) + ... + (n-X),
where n is the # rows and X = the value in B1

This is equivalent to:
A + B + C + ... + n - nX

So the cell formula is:
=SUM(A:A)-(COUNTA(A:A)-1)*B1

Take the sum of column A, subtract from it the product of the count of
non-blank cells in column A times the value in B1. Note the -1 in
COUNT(A:A)-1 is to account for the header cell "Amount".

 

[Guest]

Sorry .. to allow for header ...

=SUM(A:A)-(COUNTA(A:A)-1)*$B$1

 

[rvExcelNewTip]

thanks folks! basic algebra, I should have thought of

 

[Nick Hebb]

I don't know why, but my first post didn't come through. Try the same
as Toppers but account for the "Amount" header cell:

=SUM(A:A)-(COUNTA(A:A)-1)*$B$1