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

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.

[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.

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.

[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!



Additionally, what if the there was only one cell say A1 but instead of specifying it as A1, the address of the referred cell is actually deduced using the Index function in conjunction with the Match function to work out the row and/or the column number (assuming the formula will need to be array entered) something like so :-


INDEX(Index_200407_NSW,MATCH(TRIM($A9),TRIM(MatchCol_200407_NSW),0),1))

where Index_200407_NSW is the search array
and MatchCol_200407_NSW is the search array to match the value contained in cell A9

Now replacing the cell reference of A1 with the above, the full formula would now become something like so :-

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

I tried this but it gives an error!


Is there anything I can do to fix this or am I trying to do something that Excel does not permit!!!!!!!!!


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

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.

[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?

agarwaldvk wrote:
> 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

[Aladin Akyurek]

=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 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.

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

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.

[Aladin Akyurek]

Deepak,

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

agarwaldvk wrote:
> 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.

[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.

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 Akyurek]

Deepak,

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

agarwaldvk wrote:
> 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.

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

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.

[Aladin Akyurek]

=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 Akyurek]

=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 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.

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

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.

[Aladin Akyurek]

Deepak,

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

agarwaldvk wrote:
> 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.

[Aladin Akyurek]

Deepak,

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

agarwaldvk wrote:
> 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.

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

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.

[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.

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 Akyurek]

=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 Akyurek]

=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 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.

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

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.

[Aladin Akyurek]

Deepak,

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

agarwaldvk wrote:
> 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.

[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.

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 Akyurek]

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

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.

[Aladin Akyurek]

Deepak,

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

agarwaldvk wrote:
> 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.

[Aladin Akyurek]

=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 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.

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

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.

[Aladin Akyurek]

Deepak,

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

agarwaldvk wrote:
> 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.

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

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.

[Aladin Akyurek]

Deepak,

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

agarwaldvk wrote:
> 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.

[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.

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 Akyurek]

=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 Akyurek]

Deepak,

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

agarwaldvk wrote:
> 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.

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

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.

[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.

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 Akyurek]

=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 Akyurek]

=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 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.

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

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.

[Aladin Akyurek]

Deepak,

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

agarwaldvk wrote:
> 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.

[Aladin Akyurek]

=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 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.

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

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.

[Aladin Akyurek]

Deepak,

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

agarwaldvk wrote:
> 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.

[Aladin Akyurek]

Deepak,

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

agarwaldvk wrote:
> 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.