Hi Everybody

If I have 2 cells, say A1 and A2 on a worksheet in a workbook. Neither,
either or both of which can contain error values. In other words, none
of the two, one of two or both of them can contain error values.

I now want to add the values of cells A1 and A2 in another cell say A3
- easy enough!

The problem lies here :-

I have a very large spreadsheet with huge number of cells with existing
formula with different parameters for months, states etc. etc - the
question hence of manually changing the existing formula to include an
"if" condition is not a viable solution.

What I want to know is if there is a function (or a combination of
functions) that I can use that will perform something similar to the
"sum()" function but will ignore those arguments that result in an
error value - I should be able to globally replace "this with that" in
the selected range!

For example, the following formula is an example of the existing
formula array entered :-

=IF((--(ISERROR(INDEX(Index_200407_NSW,MATCH(TRIM($A10),T RIM(MatchCol_200407_NSW),0),1)))+--(ISERROR(INDEX(Index_200406_NSW,MATCH(TRIM($A10),T RIM(MatchCol_200406_NSW),0),1)))),"",(INDEX(Index_ 200407_NSW,MATCH(TRIM($A10),TRIM(MatchCol_200407_N SW),0),1)+INDEX(Index_200406_NSW,MATCH(TRIM($A10), TRIM(MatchCol_200406_NSW),0),1)))

This can be simplified like so :-


=IF((--(ISERROR(A1))+--(ISERROR(A2))) = 2,"", somecombofunction(A1,
A2))

The "somecombofunction" should ignore A1 or A2 if they contain error
values resulting in zero in both A1 and A2 contain error values!


Any suggestions please!!!!!!!!!


Best regards



Deepak Agarwal


--
agarwaldvk
------------------------------------------------------------------------
agarwaldvk's Profile: http://www.excelforum.com/member.php...o&userid=11345
View this thread: http://www.excelforum.com/showthread...hreadid=384426

is this what you are looking for?

=SUM(IF(NOT(ISERROR(A1:A2)),A1:A2))

array entered


--
duane


------------------------------------------------------------------------
duane's Profile: http://www.excelforum.com/member.php...o&userid=11624
View this thread: http://www.excelforum.com/showthread...hreadid=384426

duane

this may well be it. i will give it a go and see how i go. shall keep
you posted.

we may well be on a winner here!


thanks!


best regards


deepak agarwal


--
agarwaldvk
------------------------------------------------------------------------
agarwaldvk's Profile: http://www.excelforum.com/member.php...o&userid=11345
View this thread: http://www.excelforum.com/showthread...hreadid=384426

=SUM(SUMIF(A1:A2,{"0","<0"}))

agarwaldvk wrote:
Hi Everybody

If I have 2 cells, say A1 and A2 on a worksheet in a workbook. Neither,
either or both of which can contain error values. In other words, none
of the two, one of two or both of them can contain error values.

I now want to add the values of cells A1 and A2 in another cell say A3
- easy enough!
[...]

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.

Aladin

In your response (reproduced below) :-

=SUM(SUMIF(A1:A2,{"0","<0"}))

Would you still need the sum() before the sumif? Wouldn't sumif() do
the sum of the cells A1 and A2 if the condition is met?


Best regards


Deepak Agarwal


--
agarwaldvk
------------------------------------------------------------------------
agarwaldvk's Profile: http://www.excelforum.com/member.php...o&userid=11345
View this thread: http://www.excelforum.com/showthread...hreadid=384426

SumIf will return an array of 2 figures, corresponding the critera. Sum
totals that array. Otherwise, you'd just get the topleft value from the
array SumIf reurns.

agarwaldvk wrote:
Aladin

In your response (reproduced below) :-

=SUM(SUMIF(A1:A2,{"0","<0"}))

Would you still need the sum() before the sumif? Wouldn't sumif() do
the sum of the cells A1 and A2 if the condition is met?


Best regards


Deepak Agarwal



--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.

Aladin

Thanks for your quick response!

A couple of questions on that :-

First, instead of specifying a range such as A1:A2, is it possible to
have more than one range such as A1 and A3 not including A2? Would this


=sum(sumif({A1, A3},"<0", "0"))

or some other representation something like so :-

=sum(sumif((A1, A3),"<0", "0"))

not work or am I on the wrong track here?


Further, if this is not acceptable to Excel, could I specify these
multiple ranges as a named range. In other words, could I have created
a named range say "myRange" consisting of cells A1 and A3 with the
above formula becoming something like this :-

=sum(sumif(myRange, "<0", "0"))

Would that be wrong too?


Second, I still don't understand the concept of sum before the sumif. I
removed the sum() and extended the range to include A3 and it still gave
me correct answer with or without the sum() function.

Under which situations, would I necessarily be required to have the
sum() function? Please don't get me wrong - this is not to question you
but to learn more!


Best regards


Deepak Agarwal


--
agarwaldvk
------------------------------------------------------------------------
agarwaldvk's Profile: http://www.excelforum.com/member.php...o&userid=11345
View this thread: http://www.excelforum.com/showthread...hreadid=384426

Let A1 house 3 and A2 4.

Enter in B1:

=SUMIF(A1:A2,{"<0","0"})

If you select the formula on the Formula Bar and F9, you'll see:

{0,7}

However, B1 will display just 0 for you can't push an array of values
into a single cell.

In C1 enter:

=SUM(SUMIF(A1:A2,{"<0","0"}))

Now, C1 will display 7, the sum of {0,7}, so will the Formula Bar when
you apply F9 to the formula expression.

As to what to do with a non-contiguous pair of cells, i.e., A1 and A3,
you can resort to, given the fact that you just have 2 cells to evaluate:

=SUM(SUMIF(A1,{"<0","0"}),SUMIF(A3,{"<0","0"}))

agarwaldvk wrote:
Aladin

Thanks for your quick response!

A couple of questions on that :-

First, instead of specifying a range such as A1:A2, is it possible to
have more than one range such as A1 and A3 not including A2? Would this


=sum(sumif({A1, A3},"<0", "0"))

or some other representation something like so :-

=sum(sumif((A1, A3),"<0", "0"))

not work or am I on the wrong track here?


Further, if this is not acceptable to Excel, could I specify these
multiple ranges as a named range. In other words, could I have created
a named range say "myRange" consisting of cells A1 and A3 with the
above formula becoming something like this :-

=sum(sumif(myRange, "<0", "0"))

Would that be wrong too?


Second, I still don't understand the concept of sum before the sumif. I
removed the sum() and extended the range to include A3 and it still gave
me correct answer with or without the sum() function.

Under which situations, would I necessarily be required to have the
sum() function? Please don't get me wrong - this is not to question you
but to learn more!


Best regards


Deepak Agarwal



--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.