Excel 365 after applying sumif formula cant delete formula in specified cell

Copper Contributor

I have applied sumif formula and drag down, the formula went all and got the result as well. While deleting formula in specified cell or applying other formulas together with sumif in that specified cell. The cell not allowing to del or edit, after clicking space bar or back space cell became blank and once the cursor moved the formula back again. I have tried to put Zero in the cell, then all formula has disappeared. After having 4 hours session with online support, they suggested this page. I hope, some result i will get from here.Screenshot 2021-09-26 151551.jpg 

14 Replies

@Bijeesh1012 

Could you attach a copy of the workbook? You can remove irrelevant/sensitive data.

Does the formula starts with "{" and ends with "}" ?

@Bijeesh1012 

It sounds as if your yellow filled range is actually a single dynamic array, spilt from the top cell.  Such arrays are valuable just because they prevent the accidental deletion of valid data.  If you wish to delete a value, you need to reformulate the formula that gives rise to the array using

= IF(condition, SUMIFS(..., ..., ...), "").

@Juliano-Petrukio 

Dear juliano,

 

Nope, it was not nested, the online MS office agents where checking, they revert me to here. Please find the screen shotsumif.JPG

Hi Peter,
Yesterday while I was in the office i was doing the same formula with 2016 Excel, For the reconciliation purpose, I used to del specified cell. The same I was doing after purchasing 365 it is not happening. As you said if to prevent accidental deletion of valid date, how is it possible to del the specific cell formula With Vlookup and other formulas?. I will try your solution in 365

@Hans Vogelaar 

 

Hi Bro,

 

Please find the file

@Bijeesh1012 

Since the second argument of your SUMIF formula is the entire column A. Excel has spilled it to the entire column D. You can only edit and delete if you select the entire column D, and it will affect the entire column.

@Bijeesh1012 

The formula you show is greyed out because it is not actually present in the cell you are trying to edit.  The formula is in the top cell of the range and simply uses other cells to display the calculation result provided they are blank.  The whole column reference means that the formula performs over a million calculations and the used range covers entire columns.

 

To modify a term of a dynamic array, you need to build the criterion into the formula.  If you choose to omit values, it is usually a good idea to document the change to indicate the reason for the anomaly.

 

 

@Peter Bartholomew 

Sorry for the late reply, I applied some condition it doesnt work brother, The thing is that I was working with 2019 in the office and i purchase 365, i was thinking it will help me, unfortunetealy other formula was working well with my style of working but in 365 the i dnt know why only the sumif has this issue.

 

Regards

Bijeesh

I understand, the question is, what if we really need to del one specific line or adding additional formula?. I was working with 2019 and other versions why not with 365.
Thanking you so much for the file, I was trying to add some formula together with the existing formula it is not working, also trying to add some text or numbers in the column it show spill.
Dear Hans,
What is the issue cant be solved by them what is the next step, Can I ask for a refund. I would like to get an advisory from a experienced mentor like you

@Bijeesh1012 

I cannot help you with a refund, sorry.

@Bijeesh1012 

Let's start with my opinion that 365 is a far better product than any other traditional spreadsheet software that I know of.  The thing that is wrong is the working practices that 2016 allowed you to adopt and 365 prevents.  I would suggest that such working practices whereby 2+2 normally equals 4 but you may change it 5 here or there, or possibly delete it altogether is a superb way of generating errors that are difficult to detect within a large dataset.  They contribute to the fact that only about 1 in 10 spreadsheets is reasonably error free.

If you wish to override specific calculations then this is best achieved by introducing a new data column which provides a basis for the alternative calculation.  A classic example is a forecast which only shows if no actuals have been provided.

Sorry for being rude, but as I see it, it is the software that should be applying for a new user!

[don't take that too seriously but you might give it a little consideration before moving on].