Page 1 of 1
[Solved] Ignore cell value for SUM function
Posted: Mon May 19, 2025 9:26 am
by 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.
Re: Diabling cell contents
Posted: Mon May 19, 2025 10:14 am
by 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.
Re: Diabling cell contents
Posted: Mon May 19, 2025 11:41 am
by 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?
Re: Diabling cell contents
Posted: Mon May 19, 2025 12:34 pm
by 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
Re: Diabling cell contents
Posted: Mon May 19, 2025 2:36 pm
by 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.
Re: Diabling cell contents
Posted: Mon May 19, 2025 3:28 pm
by MrProgrammer
Jessiebessie wrote: ↑Mon May 19, 2025 11:41 am
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
Alex1 wrote: ↑Mon May 19, 2025 12:34 pm
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.
Re: Diabling cell contents
Posted: Mon May 19, 2025 3:33 pm
by RoryOF
I use a sheet for each month, automatically carrying the sheet balance forward to the start of the next month.
Re: Diabling cell contents
Posted: Mon May 19, 2025 3:50 pm
by Jessiebessie
Thanks for the suggestion of using an 'apostrophe'. This is much easier than using minus and plus signs.
Re: Diabling cell contents
Posted: Tue May 20, 2025 10:19 am
by keme
Jessiebessie wrote: ↑Mon May 19, 2025 3:50 pm
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.
Re: Ignore cell value for SUM function
Posted: Tue May 20, 2025 10:52 am
by Jessiebessie
Thank you for the update.
The 'apostrophe' works fine for me.