Something similar to sum() but ignoring error value arguments!

[agarwaldvk]

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),TRIM(MatchCol_200407_NSW),0),1)))+--(ISERROR(INDEX(Index_200406_NSW,MATCH(TRIM($A10),TRIM(MatchCol_200406_NSW),0),1)))),"",(INDEX(Index_200407_NSW,MATCH(TRIM($A10),TRIM(MatchCol_200407_NSW),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

 

[duane]

is this what you are looking for?

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

array entered

 

[agarwaldvk]

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

 

[Aladin Akyurek]

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

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.

 

[agarwaldvk]

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

 

[Aladin Akyurek]

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.

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.

 

[agarwaldvk]

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

 

[Aladin Akyurek]

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"}))

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.

 

[agarwaldvk]

Aladin

That's great! I now understand!

Two, what about the last bit of my query where the single cell instead
of being specified as A1 gets referred to by the use of the combination
of the 'Index() and the Match() functions like so :-

{=sum(sumif(INDEX(Index_200407_NSW,MATCH(TRIM($A9)
,TRIM(MatchCol_200407_NSW),0),1)), "<0", "0", ">0"))} - formula being
array entered!


Any clues on this????????


Best regards



Deepak Agarwal

 

[Aladin Akyurek]

Deepak,

I'm not sure what those named ranges refer to. Can you provide more
details using exact refernces?

Aladin

That's great! I now understand!

Two, what about the last bit of my query where the single cell instead
of being specified as A1 gets referred to by the use of the combination
of the 'Index() and the Match() functions like so :-

{=sum(sumif(INDEX(Index_200407_NSW,MATCH(TRIM($A9)
,TRIM(MatchCol_200407_NSW),0),1)), "<0", "0", ">0"))} - formula being
array entered!


Any clues on this????????


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.

 

[agarwaldvk]

Aladin

Sorry not providing this information!

In the formula

{=sum(sumif(INDEX(Index_200407_NSW,MATCH(TRIM($A9),
TRIM(MatchCol_200407_NSW),0),1)), "<0", "0", ">0"))} - formula being
array entered

the named ranges are in external workbook, located in the folder
"j:\dds\reports\monthly\200407\"

The name of the workbook is "Latest Monthly Report"

The named range Index_200407_NSW refers to the range "A1:G88" on the
NSW worksheet.

The named range MatchCol_200407_NSW refers to the range "C1:C88" on the
same NSW worksheet.


The idea of doing this whole exercise was to be able to read a
particular in a closed external workbook. The Index() function, as you
know, does this perfectly.
No worries here.

But when

INDEX(Index_200407_NSW,MATCH(TRIM($A9)

,TRIM(MatchCol_200407_NSW),0),1))

this returned an error value (when the search entry is not found), I
can have the return value expressed as a '0' by using
--(iserror(Index(...))) or a Blank ("") using an if clause, that's ok!

But if I have a '0' for all error values, I will have a lot many zeroes
that don't look very good when it goes to the board. It suits better in
this case if I have it expressed it (the error value) as a blank but
the problem is that when I try and add this blank subsequently to
another value it obviously returns an error value (trying to add a
number to a blank (nonnumeric value)!!!)

So what I was looking at doing was try an encapsulate this Index()
function in a sum(sumif()) type function, as you suggested, and it
worked also so long as the referred workbooks were open - which in this
scenario is not feasible (there are 24 of them). The target workbook is
a template workbook for this report with automation including automatic
range names creation for formulas and graphs etc.etc.

Any further suggestions on this!



Best regards


Deepak Agarwal