Sum of a Range (with a twist)
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".
|