Totalling only positive values in a column in Excel

Totalling only positive values in a column in...

Didn't find your answer?

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.

avatar
By rpgraham
09th Jan 2008 14:30

Sumif
SUMIF is the better way to do it:

based on Mikes example it should be:

=SUMIF(List,">0",List)

Thanks (0)
avatar
By mikeopolo
09th Jan 2008 09:07

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

Thanks (0)