Sum Function - ajit

[Div. F-II]

I have a5:a50 range with formula trim(countif(Muster!$d1:$d30,"TOI") which
results into number of TOIs in
a5:a50 but when I try =sum(a5:a50) in cell a51, I get 0.
How comes? What goes wrong?
Ajit

[Bryan Hessey]

The Trim function is a Text function, you cannot Sum text

Use a helper column and Value(A1) to A50, then Sum the helper column

[Jerry W. Lewis]

What do you get for =COUNT(A5:A50)? My guess is that you will get 0,
indicating that there are no numbers in that range to be summed (note
that "123" is different than 123, and SUM will only include the numeric
value, not the text digits).

Also, it is generally a good idea to copy/paste formulas from Excel into
your post to avoid transcription errors
=trim(countif(Muster!$d1:$d30,"TOI")
is not a valid formula, since parentheses are not matched. I can guess
that it is just missing the closing parenthesis, but am less than sure,
since it leaves me wondering why you are trying to trim a number.

Jerry

Div. F-II wrote:

> I have a5:a50 range with formula trim(countif(Muster!$d1:$d30,"TOI") which
> results into number of TOIs in
> a5:a50 but when I try =sum(a5:a50) in cell a51, I get 0.
> How comes? What goes wrong?
> Ajit

[Bryan Hessey]

As you are looking for the sum total the Count function will not assist you.
You can sum the 'trim'ed values with

=SUMPRODUCT(VALUE(A5:A50))

[Jerry W. Lewis]

If you read my post more carefully, I suggested COUNT as a diagnostic
function; if count is zero, then SUM is failing because it is not seeing
any numbers.

Jerry

Bryan Hessey wrote:

> As you are looking for the sum total the Count function will not assist
> you.
> You can sum the 'trim'ed values with
>
> =SUMPRODUCT(VALUE(A5:A50))