SUM function in range with blank cells

[litesnsirens]

I've tried using SUM and SUMIF but it's returning a value of zero and there are values in the range.
So the SUMIF formula I tried looks like this; =SUMIF('2014'!N3:N196,">0"), so on the 2014 sheet I want all the cells that are not blanks to return the sum. The SUMIF needed a criteria argument, so I included the >0.

The SUM formula I tried was simply; =SUM('2014'!N3:N196)

Both return a value of 0.00

The range N3:N196 contains numbers in some cells and blanks in others. There is no text.
I think one or both of these formulas should work but neither is.

Any ideas.

[Jonmo1]

Sounds like your numbers are not really numbers, but "Numbers Stored As Text"

What does this return
=ISNUMBER('2014'!N3)
Filled down to N196

[Ron Coderre]

Your formula is trying to sum cells containing text.
They may "look" like they contain numbers, but they don't.

Two approaches:
Convert the numeric text to numbers and use the SUM formula:
• Select '2014'!N3:N196
• Data.text-to-columns...click: Finish

or
Coerce the text to numbers within the below formula:

Please Login or Register  to view this content.

Is that something you can work with?

[litesnsirens]

That was the issue. The values have been converted to numbers and the simple SUM formula is working. Thanks to both of you.

[Jonmo1]

You're welcome.