How to SUM TWO ARRAY formula results

[Guest]

Hi,

I have a few array formula's. I want to SUM the result from them in another
cell. But it always shows up as zero?

My formula in C18 and C19 etc.. :

=RIGHT(C18;LEN(C18)-MATCH(FALSE;ISERROR(1*MID(C18;ROW($1:$100);1));0)+1)

And my sum as usual.. =SUM(C18+C19)

I'm no big user of arrays. Is this some sort of limitation?

Thanks for any help!

 

[Stephen]

Hi,

I have a few array formula's. I want to SUM the result from them in
another
cell. But it always shows up as zero?

My formula in C18 and C19 etc.. :

=RIGHT(C18;LEN(C18)-MATCH(FALSE;ISERROR(1*MID(C18;ROW($1:$100);1));0)+1)

And my sum as usual.. =SUM(C18+C19)

I'm no big user of arrays. Is this some sort of limitation?

Thanks for any help!

Your problem has nothing to do with C18 and C19 being array formulas.
Rather, it is that the results are text strings rather than numbers, which
are fundamentally different in Excel. I can see straight away that the
results are text strings because RIGHT is a function that returns a text
string, not a value. Excel ignores text strings when doing arithmetic -
hence your answer is zero.

To overcome this, you need either to rewrite the formulas so they return
numbers, or convert the text strings to numbers as you add them. The latter
is the simplest way from where you are (but may not be the ideal answer - I
don't know what you are trying to achieve). Instead of your SUM formula, use
=VALUE(C18)+VALUE(C19)

By the way, there's no need for SUM when you have only two numbers to add.
=C18+C19
is just the same.

 

[David Biddulph]

Question 1.
Why do you use =SUM(C18+C19) ? What is wrong with =C18+C19 [or
=SUM(C18,C19) ]?
SUM is a function which will add a number of arguments, but you have given
it only one argument (C18+C19). Saying SUM(C18+C19) is as pointless as
saying =MAX(C18+C19) or =PRODUCT(C18+C19) or =AVERAGE(C18+C19) or
=MEDIAN(C18+C19) or ...

Question 2.
What values do C18 and C19 return? An initial worry is that RIGHT is a text
string function, and returns a text string, not a number. Usually if the
contents of the strings are strings that represent numbers, then =C18+C19
would happily convert them to numbers and add them. If the strings don't
represent numbers I would expect a #VALUE error, rather than zero. It might
be worth seing whether using =--RIGHT() gives you a different result from
=RIGHT(). If that doesn't solve it, look carefully at what C18 and C19
contain. Are there hidden non-printing characters in your text string?

 

[Stephen]

Question 2.
What values do C18 and C19 return? An initial worry is that RIGHT is a
text string function, and returns a text string, not a number. Usually if
the contents of the strings are strings that represent numbers, then
=C18+C19 would happily convert them to numbers and add them.

A further insight:

For C18 and C19 being text strings:
=C18+C19
will convert and add them.

However,
=SUM(C18:C19)
will not convert them, thus giving the result zero.

 

[Guest]

Thank you both for the suggestions. I may not have been clear enough. The
reason I wanted to use SUM is because I need to add up values from 365 cells.
I've tried =VALUE(C18)+VALUE(C19) but the result is an error #VALUE. Also
tried --RIGHT instead of RIGHT, resulting in an error as well.

Some insight to what I'm trying to do here.

The file tracks employee absence. Employee ID is in cell B1, days in A2
downwards. From Cell B2 down, you can enter "LA 0.5" for example. Indicating
the employee was 0.5 hours LAte. Instead of LA there can be 1 other word
(OTH). In the totals I summarize how many occurence of LA have been found, so
I can see how often this person was late. I also want to know His total
lateness. For this I want to split LA from the 0.5. These must be in one
cell. Splitting the cell (LA in one, 0.5 in another) seems easier but it's
not an option.

NAME HIDDEN COLUMN1 HIDDEN COLUMN2 HIDDEN C3
DAY1 LA 0.5 ARRAY to extract 'value' IF B2=LA*, B2, 0 IF
B2=OTH*,B2,0
DAY2
DAY3

I want to sum hidden column 2 and hidden column 3 somehow. Either by
changing my array formula to return values or some other way.

Hopefully I've explained it clear enough. Thank you for your thoughts on this!