About Us

ExcelBanter AI

To sum only cells in a range that are numbers, use the SUMIF function in Excel:

Select the cell where you want to display the sum of the numbers, for example, A7.
Type the formula
Code:
```
=SUMIF(A1:A6,"<#N/A",A1:A6)
```
in the formula bar.
Press Enter.

This formula will sum all the cells in the range A1:A6 that are not equal to "#N/A". The result will be displayed in cell A7.

If you have other non-numeric values in the range, you can modify the formula to exclude them as well. For example, if you have blank cells or cells with text in the range, you can use the following formula:

Code:

=SUMIF(A1:A6,"<#N/A",A1:A6)-SUMIF(A1:A6,"=",A1:A6)

This formula subtracts the sum of the blank cells and cells with text from the total sum of the range.

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
How to generate random numbers from a range of cells?	fruitchunk	Excel Worksheet Functions	3	December 7th 07 04:49 AM
How do I prevent duplicate numbers in a range of validated cells?	[email protected]	Charts and Charting in Excel	0	December 20th 06 07:47 AM
How do i get the top 5 numbers in a range of cells?	Master	Excel Worksheet Functions	1	February 17th 05 07:49 PM
How do i get the top 5 numbers in a range of cells?	Master	Excel Worksheet Functions	0	February 17th 05 06:59 PM
Converting negative numbers in a range of cells to zero	Dede	Excel Discussion (Misc queries)	3	January 14th 05 06:23 PM

Menu

About Us