[Solved] Ignore cell value for SUM function

[Jessiebessie]

There are some cells which have a content that I do not want to include in a column sum but do not want to delete.
How do I disable the contents of a cell without deleting it.

Edit: Changed subject, was Diabling cell contents [sic] Make your post understandable by others  -- MrProgrammer, forum moderator

[keme]

If it is particular cells, same cell all the time, the simple way is to omit the cells from the summation range. You can specify multiple ranges inside the SUM() function.

For most situations matching your request it is better to use some kind of SUMIF() construct.

How to go about it, and whether you need additional measures, depends on context. What, specifically, are you trying to do? What is it that signifies particular values as "not for summing"?
E.g.:

• The value itself
  • too large
  • too small
  • negative
  • matching some other cell
• Another cell (same row) denoting the entry "irrelevant"
  • Some row title, like "Partial sum"
  • A tick box for manual (de)selection of summation entries

A sample file with your data may also help us to help you. Remember that this is a public place, so you should mangle/remove any confidential content.

[Jessiebessie]

Here is a little more detail of what I am trying to achieve.
This is a bank statement spreadsheet covering 12 months in which each column is a month.
Each month is totaled at the bottom.
Some of the cells contain monthly payments which I only want to enable as and when they have been made.

Is this helpful?

[Alex1]

It's easier to put all amounts in one or two columns (in and out), and to add columns for date, category, etc.
Use a pivot table to split by category and date, then group the dates by month and year.
See download/file.php?id=49408

[keme]

Attached is a sample illustrating different approaches to achieving partial sums by formula. Single column, but the SUMIF() can be easily adapted to multiple "per month" columns, if you must.

Note that the pivot table suggested by Alex1 may be a better choice. That will also reveal typos (typically inadvertent addition of leading/trailing spaces) which may mess up conditional sums.

[MrProgrammer]

Some of the cells contain monthly payments which I only want to enable as and when they have been made.

The SUM function will ignore cells with text values. Indicate that a cell contains text by prefixing the value with an apostrophe. Remove the apostrophe to change the value to numeric so SUM will add it. Read about the apostrophe in the Calc Guide. It is the cell value (numeric versus text) which is important here, not the cell format.
[Tutorial] Ten concepts that every Calc user should know

It's easier to put all amounts in one or two columns (in and out), and to add columns for date, category, etc.
Use a pivot table to split by category and date, then group the dates by month and year.

Agreed. Using a layout with a column for each month will make it cumbersome to use in Calc. Using separate sheets for each year will make the spreadsheet even more awkward. Use a filter to show the data of interest, say a particular month or year. Use a pivot table to summarize data instead doing that with formulas. Pivot tables are one of the most important features in Calc. There are thousands of topics on the forum from people who struggle to perform calculations until their data is organized so that they can use Calc's features effectively. You cannot use pivot tables if you create a column for each month or if you distribute your data onto multiple sheets. Read about filters and pivot tables in the Calc Guide.

There are certainly hundreds of topics in the Calc forum about using a spreadsheet for expense tracking. You could read some of those and perhaps find a quick solution for yourself. And there is a Templates site which may have a spreadsheet you could use without having to create it yourself.

If this solved your problem please go to your first post use the Edit ✏️ button and add [Solved] to the start of the Subject field. Select the green checkmark icon at the same time.

[RoryOF]

I use a sheet for each month, automatically carrying the sheet balance forward to the start of the next month.

[Jessiebessie]

Thanks for the suggestion of using an 'apostrophe'. This is much easier than using minus and plus signs.

[keme]

Thanks for the suggestion of using an 'apostrophe'. This is much easier than using minus and plus signs.

You may then need to be aware of the implications of using "wrong" data type to trick the application:

• Summation by operators (=A1+A2) will yield different result from summation by function (SUM(A1:A2))
• Values may jump around.
  Default alignment is left for text and right for numbers.
• Custom cell format (whether explicitly applied or implied by previous input) may be lost or behave badly.
  E.g. thousands separators, currency sign.

For a quick solution to whatif-problems and similar situations, the data type change by way of apostrophe is indeed a quick and easy method. (I confess, I use it myself sometimes.)

If you are making a spreadsheet for future/long term use, and/or intended for others to access (spouse, boss?), using functions explicitly created for the purpose is safer.

[Jessiebessie]

Thank you for the update.
The 'apostrophe' works fine for me.