SOLVED

# Array formulas and spilling

Occasional Contributor

# Array formulas and spilling

Is there a way to test for a value in a range of cells and then, if some of the cells are not empty, send the sum of the non-empty cells to another single cell? I have been experimenting with formulas to accomplish this but always end up with a "Spill."

8 Replies

# Re: Array formulas and spilling

Giving more detailed examples and better yet a sample spreadsheet helps a lot. I'm really not sure I understand what you are asking for but will give it a shot.
First off, your first part is to check for a particular value in a range of cells and then you say IF some are not empty, which has to be the case if you found that value then at least 1 is not empty, right? Unless that value is a text value in which case it is 'empty' w/r to a sum.
Next you say you are getting "Spill" error which indicates you are getting an array of values back (instead of a single value) but there is already data in the cell(s) nearby that would be required to show that array of data.
Lastly, you say "send the sum .. to another single cell" but Excel does NOT work like that. A formula in an Excel cell defines what value to show in that cell (not counting the spill over due to returning arrays).
In any case here is what I think you are asking:
=IFERROR(IF(MATCH(V1,R1:R5,0),SUM(R1:R5),""),"")
where V1 is the value you are looking for and R1:R5 is the Range of cells.
best response confirmed by David Lambert (Occasional Contributor)
Solution

# Re: Array formulas and spilling

...sum of the non-empty cells to another single cell

in C1:

``=SUMIF(A1:A8,"<>",A1:A8)``

That would work even if empty cells contain something like: =""

# Re: Array formulas and spilling

As variant

``=IF(ISNA(XMATCH(,A1:A20)), "no blank cells", SUM(A1:A20))``

if you mean blank cells, not cells with empty string "".

# Re: Array formulas and spilling

This works too. Thank you.

# Re: Array formulas and spilling

Perfect! Does exactly what I want it to do. This works with the 'Average' function as well. Thank you.

# Re: Array formulas and spilling

Well, this produces the same result:

``=SUM(A1:A8)``

# Re: Array formulas and spilling

Perhaps I misunderstood the question: to sum only if there are no blank cells in the range, if at least one blank cell then return something else.

Otherwise that's as @Detlef Lewin suggested.

# Re: Array formulas and spilling

If this works as you expect I probably misunderstood something as well => Use Detlef's one:

``=SUM(A1:A8)``

Same goes with Average

``=AVERAGE(A1:A8)``

Could you unmark the current Best response and mark the one from Detlef instead please?

Thanks & nice day...