Array formulas and spilling

Occasional Contributor

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
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:
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)

@David Lambert 

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


in C1:



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


@David Lambert 

As variant

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

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

This works too. Thank you.

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

@David Lambert 

Well, this produces the same result:


@David Lambert 

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.

@David Lambert 

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


Same goes with Average



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

Thanks & nice day...