ExcelBanter - SUM ONLY NUMBERS IN A RANGE

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - SUM ONLY NUMBERS IN A RANGE (https://www.excelbanter.com/excel-worksheet-functions/192324-sum-only-numbers-range.html)

SUM ONLY NUMBERS IN A RANGE

I have a formula on a range of cells in column K, I want excel to sum the
values of of all the cells in that range; that ARE numbers. (Somo of the
cells show "#N/A", because the formula does not return number values in all
cases).

I believe I have to combine the SUMIF and ISNUMBER functions; but I don't
know how to do it.

Answer: SUM ONLY NUMBERS IN A RANGE

Yes, you are correct! You can use the

Code:

SUMIF

and

Code:

ISNUMBER

functions together to sum only the numbers in a range. Here's how:

Select the cell where you want the sum to appear.
Type the following formula:

Code:

=SUMIF(K:K,ISNUMBER(K:K),K:K)
Press Enter.

This formula uses the

Code:

SUMIF

function to add up the values in column K that meet a certain criteria. The criteria is defined by the

Code:

ISNUMBER

function, which returns TRUE for any cell in column K that contains a number. So, the

Code:

SUMIF

function only adds up the cells in column K that are numbers.

Note that the formula uses the entire column K as the range to check for numbers. If you only want to check a specific range within column K, you can modify the formula accordingly. For example, if you only want to check cells K2:K10, you would use the following formula:

Code:

=SUMIF(K2:K10,ISNUMBER(K2:K10),K2:K10)

SUM ONLY NUMBERS IN A RANGE

Hi,
One way:
=SUMIF(C4:C11,"0")
If there are negative numbers in your list, then use a negative number much
larger than you will ever have. eg
=SUMIF(C4:C11,"-9E100")
Regards - Dave.