I received a number of emails from people who read my last newsletter about my tip on summing a column containing error messages so I thought I would share it here with you.
SUMMING A COLUMN CONTAINING ERROR MESSAGES
If you don't already know how to do this, then this should make your year because everyone runs into it at some point.
In Excel 2003, you can use the SumIF function to tell Excel to sum all the cells in a column if they are greater than 0. By doing this, Excel will ignore any error messages, such as #N/A and DIV!0 in the column.
In the example, below, I am adding up column B and telling Excel to sum the numbers if they are greater then 0.
If you don't already know how to do this, then this should make your year because everyone runs into it at some point.
In Excel 2003, you can use the SumIF function to tell Excel to sum all the cells in a column if they are greater than 0. By doing this, Excel will ignore any error messages, such as #N/A and DIV!0 in the column.
In the example, below, I am adding up column B and telling Excel to sum the numbers if they are greater then 0.
What to do if you have negative and positive numbers in the column? Simply add another SUMIF that tells Excel to add just negative numbers.
As with everything, there is more than one way to do this. A more elegant way is to use an array and I will talk about that tomorrow.
Have a great day and keep looking... the sun is out there somewhere
No comments:
Post a Comment