I have a column of numbers. I sum them by formula and store the answer in a cell at the bottom of the column. In doing so, I want to put a minus sign (-) behind or in front of a selected number in the column so it won’t be calculated in the sum but still be visible. For years in Microsoft spreadsheets I could keep my column of bills paid/unpaid by placing a minus sign (-) behind the numbers as each cleared the bank. When I started the list of numbers each would be a positive value. As each cleared the bank it would become a negative value. The decreasing value would automatically appear in the bottom cell of the column, as the formula corrected the sum after each changed entry. In Libre Office I’m getting inaccurate results in the bottom cell, and I haven’t been able to figure out what the sum function is doing. Other then a negative sign/minus sign (-) is there some other symbol I am supposed to use? The main idea is to keep that number in the cell for reference but it not be added when summing the column of cells.
If I understand your scenario correctly, your column starts with positive numbers & the column is totalled. When an item clears you change it to a negative value & do not want it calculated in the total any more. If so, this should work:
=SUMIF(A1:A5,">=0")SUM of zeroes? May be you mean =SUMIF(A1:A5,">0") ? (only “greater than”, not “equal”)
Either will work.
Isn’t this a clear misuse of a sign? After all signs are meaning something by common understanding. Two easy ways: Why violate the standard?
Two ways:
- If a number to exclude from the sum is entered with a preceding Apostrophe it will be treated as a text and SUM will ignore it. The entry will also be aligned left in the cell if ‘Standard’ alignment is working.
- Additional column to indicate “don’t include” by “–” e.g. Then
=SUMPRODUCT(A1:A5;B1;B5<>"--")
Second way better!