How to quickly sum a column cells ignore #N/A errors in Excel?
If there is a column list as below screenshot shown, and now you want to sum the whole column and ignore #N/A errors. Here I introduce some tricks to quickly solve this job in Excel.
Select Cells with Error Value (quickly select non-blank cells from a selection or whole sheet.）
|Never need to worry about long long formulas in Excel anymore! Kutools for Excel's Auto Text can add all formulas to a group|
as auto text, and liberate your brain! Click here to know Auto Text Click here to get free trial
There is a formula can help you quickly sum up the column ignore #N/A.
Select a blank cell, C3 for instance, and type this formula =SUMIF(A1:A14,"<>#N/A"), press Enter key to get the result.
Tip: in above formula, A1:A14 is the column list you want to sum up, you can change it as you need.
If you usually use above formula, and you do not want to waste time to search the formula every time or to remember it, you can use Kutools for Excel’s Auto Text function to add above formula =SUMIF(A1:A14,"<>#N/A") as an auto text entry, and apply it in any time you need.
|Kutools for Excel, with more than 120 handy Excel functions, enhance working efficiency and save working time.|
After free installing Kutools for Excel, please do as below:
1. Click Kutools > Insert > AutoText to activate the AutoTextpane. See screenshot:
2. Then double click at the formula cell, and go to the formula bar, and select the formula, and click to add it. See screenshot:
3. In the New Auto Text dialog, specify a name for this new auto text, and then select a group to put it. See screenshot:
4. Click Add to successfully add this formula to the auto text pane. And from now on, when you need this formula, you just need to select a blank cell, and find this formula in the auto text pane, and click to insert it. You can change the cell reference to meet your need.
You may be interested in:
- How to count cells with zeros but not blanks in Excel?
- How to count the number of occurrences of a word in a column in Excel?
- How to count unique/duplicate dates in an Excel column?
- How to find the max or min value for each day in an Excel range?
You are guest
or post as a guest, but your post won't be published automatically.
- To post as a guest, your comment is unpublished.· 2 months agowhen i need to do auto sum on excel its not working
- To post as a guest, your comment is unpublished.· 1 years agoterima kasih atas sharing ilmunya, sangat bermanfaat. Semoga Allah membalas anda dengan Kebaikan.
- To post as a guest, your comment is unpublished.· 11 months agoExcuse me? Could you repeat your question in English?
- To post as a guest, your comment is unpublished.· 9 months agoHe said thanks for the sharing of knowledge as it is very useful. May Allah bless you for your kindness