Ignore errors in SUM

[Watchamacallit]

Hi guys.

I'm putting together a spreadsheet and for the life of me i can't get a simple total sum/calculation to work. I've took a screenshot so you guys can see what i'm tryin to achieve.

Essentially, i want to add a column together, including both negative sums (in red) and positive (in green) to give me a combined total in a lower box. However, it keeps spitting out errors at me because of errors found in other rows.

I basically need a formula to ignore rows with errors and just add up all other rows. Can anyone write me one out quick? I'd really appreciate it.

Title Edited. A descriptive title for posts helps others who are searching for solutions and increases the chances of a reply [robleyd, Moderator].

[robleyd]

It might be easier to not have errors in the first place You haven't indicated what the formulae in G, H and I are, but you could use IF and ISBLANK to only perform those calculations if there is data in whichever of A-F causes the divide by zero error. Something along the lines of:

Code: Select all

IF(ISBLANK(E5); ""; <your formula here>)

[Watchamacallit]

Hmm, not sure i understand. I've attached the file so you can maybe see for yourself what im trying to achieve. Aside from the basic maths behind it thats really all i can do - i don't know the functions or their capabilities so there's probably a far better way around it that i'm not seeing. Ignorance isn't bliss. lol

[robleyd]

See the attachment where I've modified the first week according to what I think you want. Note that the structure you have is going to get a little unwieldy when you have hundreds of weeks of data. You might consider adding a first column containing the date; you can then use tools like Pivot Table to summarise your data.

Record_Weekly.ods

(13.52 KiB) Downloaded 77 times

There is a comprehensive list of functions here in the Wiki. If you are new to spreadsheets, you may find the following to be useful resources.

[Tutorial] Ten concepts that every Calc user should know
OpenOffice Spreadsheet Tutorial for Beginners with Examples

[MrProgrammer]

I basically need a formula to ignore rows with errors and just add up all other rows

See attachment, sheet Direct answer to your question. However I don't recommend that. It's better to avoid the #DIV/0! errors in the first place, as robleyd has demonstrated.

201906071027.ods

(27.57 KiB) Downloaded 79 times

Note that the structure you have is going to get a little unwieldy when you have hundreds of weeks of data.

I agree. Also, the spreadsheet is formatted to death. See attachment, sheet How I would organize the data. It includes a Pivot Table. If you want to spend your time formatting, use styles, not Format → Cells.

Aside from the basic maths behind it thats really all i can do - i don't know the functions or their capabilities so there's probably a far better way around it that i'm not seeing.

At the very least, read the Ten Concepts tutorial.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.