View Single Post
  #4   Report Post  
Posted to microsoft.public.excel.programming
Nick Hebb Nick Hebb is offline
external usenet poster
 
Posts: 162
Default 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".