Use non-contiguous cell references in SUMIF function's first parameter

Question

I'm trying to add (sum) values with some conditions over an arbitrary non-contiguous cell range in a spreadsheet.

Example:  I want to add the negative values in A1, C2, E1, K42 and M5.

Progress so far:  A more patterned example is summing values in A2, C2, E2, G2 and I2 with the condition that the values are negative.  (Note: I don't want to sum the columns that are not mentioned in the above sequence.)  I tried doing this in LibreOffice's spreadsheet and found that it supports the ~ operator where a non-contiguous cell range is required.  The above (second) example can be handled in LibreOffice with SUMIF(A2~C2~E2~G2~I2,"<0"); illustration:

Unfortunately this is not supported in MS Excel (or may be not supported in the version that I own).

Updated Note

A sufficiently patterned region (like the second example, above) can be handled with SUMIFS with the cells selected by a mathematical function.  Assume that I'm dealing with a region that cannot be so selected.  It was a simple example!  The question wants to address the possibility to select any cells anywhere in the worksheet.

Answer 1

From the edited example you can use Excels indirect function to set your referenced cells.

INDIRECT({"A2","C2","E2","G2","I2"})

then use the SUMIF Criteria "<0" to get the required vaues to SUM.

Put these together and you get your required formula.

=SUM(SUMIF(INDIRECT({"A2","C2","E2","G2","I2"}),"<0"))

Answer 2

Your answer works, but it is a work around.  It clearly becomes unwieldy if you want to handle A1, C1, E1, G1, I1, K1, M1, O1, Q1, S1, U1, W1 and Y1.  A general solution is to use a helper row.  Pick a row that you aren’t using; it might be Row 100, or Row 1 on another sheet.  (Let’s assume that you use Row 100.)  Enter =MOD(COLUMN(),2) into Cell A100 and drag/fill to the right to H100 (or Z100, or wherever).  Then use

=SUMIFS(A1:H1, A100:H100, 1, A1:H1, "<0")

to compute the sum you want.  The A100:H100, 1 part selects (i.e., restricts the sum to) the cells where the column number is odd (i.e., where MOD(COLUMN(),2) is 1).  The A1:H1, "<0" part, obviously, selects only the negative numbers from the odd cells in Row 1.

Of course you can expand this to as many columns as you want.  If you don’t want to dedicate a row to use as a helper row (for example, because your data can grow vertically without bound), you can use something like AA1:AZ1 as your helper row.

Here’s a screenshot:

You can see that A2 = -105 = (-100) + (-4) + (-1) = A1 + C1 +G1.  Of course E1 is skipped because its value is positive.

Answer 3

If you put the following into a Named Range:

=ADDRESS(ROW(INDIRECT(SORT(FILTERXML("<Outer><Inner>"&SUBSTITUTE(A16,",","</Inner><Inner>")&"</Inner></Outer>","/Outer/Inner")))),COLUMN(INDIRECT(SORT(FILTERXML("<Outer><Inner>"&SUBSTITUTE(A16,",","</Inner><Inner>")&"</Inner></Outer>","/Outer/Inner")))))

then that Named Range can be used as your reference in SUM().

Trying to use it directly in a cell/SPILL range always resulted in a #VALUE! error for me. Reasonably sure that can be overcome, but I haven't managed that yet. In any case, putting it into the Named Range solves it so...

Perhaps the Named Range is cow. If so, then the following formula works for summing the negative values in the chosen cells:

=SUM((cow<0)*cow)

(The above uses a TRUE/FALSE test whose results are converted to 1's and 0's by the multiplication. That multiplication gives 0's for the failed items in the range and the value of the successful cells since 1*cell value = cell value. SUM() then adds it all to give you the desired result.)

The first formula assumes you enter the list of cells to work on in A16 (adjust to fit your actual entry cell). It uses the FILTERXML() approach to convert that to a SPILL list of cell addresses (as strings, not references yet) and sorts them (not necessary to this, just something I did along the way for an earlier idea and did not remove... causes no harm either though and might be useful if ever wanting to extract more from the situation). ADDRESS() then uses those strings, washed through INDIRECT() (more on that in a moment), to make references that Excel will recognize. This is all in the Named Range and the Named Range then outputs a list of their values.

Weirdly, INDIRECT() seems necessary. Seems like one could remove it, but then one gets the #VALUE! errors. Or use only it, no ADDRESS() required, but that fails too, same error. But so long as it is wrapped in another function, it seems to work. I've noticed something similar in that even functions that ostensibly return a single cell, brayed about repeatedly for decades by MS, the notorious "upper left" cell of a range, actually do produce an array using the whole range and if wrapped in (almost) any other function, will pass that ENTIRE array onto the outer function, not just the single value it supposedly ought to produce. INDIRECT() seems unwilling to work properly, somehow, with the SPILL functionality here, but will pass the whole array, in working condition, onto the ADDRESS() function.

Like with needing to use the Named Range rather than putting it all into the final formula like one would desire, I'm sure there's deeper layer of understanding I don't have yet, and someone else might add to the question here to explain and resolve both things. One hopes.

Naturally, the first formula might usually benefit nicely from using LET() but since it's in a Named Range, I didn't bother. One should edit it to neaten it up that way, for future maintenance of the spreadsheet. It's hidden in the Named Range, but for maintenance purposes, one would copy it out and then readability and understanding would count for a lot.

One's non-contiguous cell list could be built many ways. I assumed a simple listing of the desired cells, like one might enter it as a reference in a formula: A1,M5,C2,E1,K42

Naturally, it can be used in the old school manner of using arrays to do what SUMIF() and friends do.

But outside the immediate question, the approach does seem like a workable way to shift such a selection of non-contiguous cells into being a working reference.

I just looked at one of the "related questions" to the side here (Multiple conditions in if statement on non-contiguous cells in Excel) which talks about a couple of ways to make such into a usable Named Range, but I cannot make it work either way. An answer in it by Máté Juhász show that COUNTIF() and SUMPRODUCT() should work once it is a Named Range.

A bit related, but just as putting everything into a cell formula gets the #VALUE! error, so does defining a name in LET() and using it the functions calculation. So no easy end run there.

Answer 4

Was able to do this with a work around

=SUM(IF(A2 < 0, A2, 0),IF(C2 < 0, C2, 0),IF(E2 < 0, E2, 0),IF(G2 < 0, G2, 0))