Forum Discussion
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."
...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: =""
8 Replies
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.
...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: =""
Perfect! Does exactly what I want it to do. This works with the 'Average' function as well. Thank you.
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...
- 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.
