Forum Discussion
Solved
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 formula...
...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: =""
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.
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.