Creating an array from several seperate cells...

**levo_redkid** · 03-05-2008, 04:48 PM

Hi all,
What I am trying to do is, to sum up the values in an array, given that the cell value is not an error.
If the cells were in order, the following array formula would solve it easily:
{SUM(IF(ISERROR(A1:A3),0,A1:A3))}

But my problem is that, my cells are not in order. To be more specific, I want to look at A1, B12 and C13, and sum them up with an array formula given the condition that cell value is not an error. Of course, in my case, I have too many cells.

Can anyone please help me with this??

Many thanks,
-levent

**protonLeah** · 03-06-2008, 01:26 AM

Why do you need an array formula instead of something like:

Please Login or Register  to view this content.

**Smirgelius** · 03-06-2008, 01:28 AM

This is not to spam your thread with my problem, but to help and add synergy, as I think my problem is exactly the same as yours. So if my problem is solved, I think your problem is solved, and vice versa. Thus, I think you should check this thread as well:
http://www.excelforum.com/showthread.php?t=636089

ed:

Originally Posted by protonLeah

Why do you need an array formula instead of something like:

Please Login or Register  to view this content.

Okay, I guess though the problem was originally pretty much the same as mine, for levo_redkid's problem there may also be an alternate solution.

shg: thanks, I fixed the link now.

**shg** · 03-06-2008, 02:15 AM

Smirg, your link points to this thread.

Ben, I don't think your post does what levo asked -- sum the non-error values.

Levo, why not fix the cells so they don't return errors?

**levo_redkid** · 03-06-2008, 04:26 AM

Ok, thanks for the suggestions but it wouldn't help. Let me be more specific and insert a file here.

All the explaination in the file, thanks for the help..

-levent

**rylo** · 03-06-2008, 04:59 AM

Hi

H6:

=SUM(IF(ISERROR(L19:L35),0,L19:L35),IF(ISERROR(P19:P35),0,P19:P35),IF(ISERROR(T19:T35),0,T19:T35),IF(ISERROR(X19:X35),0,X19:X35),IF(ISERROR(AB19:AB35),0,AB19:AB35))

Array enter the formula

Is that what you are chasing???

rylo

**Smirgelius** · 03-06-2008, 06:02 AM

I've talked to a couple of people about this problem, and it seems to me that even though you redkid might be able to go around the problem (eg. perhaps with rylo's suggestion), excel just isn't capable of doing the thing you and I would want it to do, even though it seems so utterly simple.

Another way around the problem is of course making copies of the cells you want to add to the formula. And I suppose I'm obliged to do just that.

It's pretty unbelievable if Excel's functions are truly so limited, but I guess I'll just have to cope with that.

**levo_redkid** · 03-06-2008, 07:59 AM

thank you everyone. I'll try to figure out another way around this problem. Thanks..

**shg** · 03-06-2008, 01:38 PM

Maybe this UDF will help. For example

=SumSansErrors(1/0, sqrt(-3), 10, "1") = 11

Or for your case, SumSansErrors(L20, P20, T20)

In a code module:

Please Login or Register  to view this content.

**levo_redkid** · 03-06-2008, 05:44 PM

Hi Shg,

Thanks for the answer, unfortunately I do not know how to insert user defined functions, but tomorrow I will try. I'll let you know if I can sort it out..

Regards,
-lev.

**shg** · 03-06-2008, 05:56 PM

Do Alt+F11 to open the editor, from the menu bar do Insert > Module, and paste the code in the window that appears. Close that all out and return to Excel.