I have a column of figures in an excel spreadsheet. Some are positive (representing deposits into a bank account), some are negative (representing withdrawals) and some cells are blank (representing no activity).
The total of the column is the net movement in the account - but I wish to know the gross total of the deposits in the column.
I can see a way to get this by using an IF statement to copy each cell in the column to a new column, but only if the value is greater than zero. I can then total the new column to get the total of the deposits only. But this seems rather clumsy.
Is there a better way?
David Winch
Replies (2)
Please login or register to join the discussion.
Sumif
SUMIF is the better way to do it:
based on Mikes example it should be:
=SUMIF(List,">0",List)
array sum formula
Try =SUM(IF(List>0,List,FALSE))
entered as array formula (ctrl+shift+enter)
Name the range first as List, makes it easier to create.
This from http://www.cpearson.com/excel/lists.htm.
Regards
Mike