SUM Veritical/Horizonal

Discuss the spreadsheet application

SUM Veritical/Horizonal

Postby MCR » Fri Apr 26, 2019 10:18 pm

This is probably the stupidest question ever.. But I have a large spreadsheet, linked from multiple other sheets.

Column a is the vendor, category-then the rest of the column are the months in a year, and the last row is sum for the year by that vendor
Now when I sum that last column (the Vendor total by the end of the year)-vertically I get $241,765.43

On the very bottom, There's a sum for each month, all vendor, no matter the vendor-and sum horizontally through this total line and receive $284,387.54.
Hope I explained this well!

They're summing the same exact information. So why is it different.

I've spent all day frying my eye balls, double checking each link, formula, and everything is as it should be.

In another places, I sum this same data again, using a sumif by the Category for the criteria and the yearly Total for the sum range.
I then summed all the categories, and it matches the $241,765.43

But I Don't understand why the horizontal summation is Completely different. All the formatting for dates, categories, totals etc match.
Last edited by MCR on Fri Apr 26, 2019 10:24 pm, edited 1 time in total.
Windows 10 Pro 1809 / OpenOffice 4.1.5
MCR
 
Posts: 8
Joined: Fri Apr 12, 2019 11:10 pm

Re: SUM Veritical/Horizonal

Postby RoryOF » Fri Apr 26, 2019 10:20 pm

Check your data types, using /View /Value highlighting.
Apache OpenOffice 4.1.7 on Xubuntu 18.04.3 (mostly 64 bit version) and very infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 29923
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: SUM Veritical/Horizonal

Postby MCR » Fri Apr 26, 2019 10:47 pm

It does pull up two different colors for the values, however the difference is some are linked, some aren't.

I went back and double checked, the blue, are all manually entered into that spreadsheet, and are the same format
as each linked sheet which appears in green.
Windows 10 Pro 1809 / OpenOffice 4.1.5
MCR
 
Posts: 8
Joined: Fri Apr 12, 2019 11:10 pm

Re: SUM Veritical/Horizonal

Postby RoryOF » Fri Apr 26, 2019 10:51 pm

The only possibility that occurs to me is that some of the horizontal or vertical sums are missing a start or end cell, perhaps a typo in the start or end specification of the sum command.
Apache OpenOffice 4.1.7 on Xubuntu 18.04.3 (mostly 64 bit version) and very infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 29923
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: SUM Veritical/Horizonal

Postby MCR » Fri Apr 26, 2019 11:17 pm

I tried this, and re did all the summation columns, same result I've never had this happen before but Im going to loose my mind.
Windows 10 Pro 1809 / OpenOffice 4.1.5
MCR
 
Posts: 8
Joined: Fri Apr 12, 2019 11:10 pm

Re: SUM Veritical/Horizonal

Postby RoryOF » Fri Apr 26, 2019 11:48 pm

It will all be easier after a good night's sleep. Leave it for the time being.
Apache OpenOffice 4.1.7 on Xubuntu 18.04.3 (mostly 64 bit version) and very infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 29923
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: SUM Veritical/Horizonal

Postby RusselB » Sat Apr 27, 2019 3:00 am

Do the amounts in the links agree with what the referenced amounts are?
Are the referenced sheets part of the same spreadsheet or are they one (or more) different spreadsheets?
If the links reference different spreadsheets, then the data that the links use is the last data for that spreadsheet that was saved
If you have spreadsheet A open and referencing spreadsheet B, and make a change to the data in spreadsheet B, that change will not be reflected in spreadsheet A until you have (re-)saved spreadsheet B and then updated the links.
OpenOffice 4.1.7 and LibreOffice 6.3.3.2 on Windows 7 Pro & Ultimate
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
RusselB
Moderator
 
Posts: 5703
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: SUM Veritical/Horizonal

Postby MrProgrammer » Mon Apr 29, 2019 4:25 pm

MCR wrote: have a large spreadsheet, linked from multiple other sheets. … When I sum that last column (the Vendor total by the end of the year)-vertically I get $241,765.43. On the very bottom, There's a sum for each month, all vendor, no matter the vendor-and sum horizontally through this total line and receive $284,387.54.
If you can't find the mistake yourself, we need to see your spreadsheet. Is this all in one file? If so, attach a document demonstrating the difficulty (remove confidential information then use Post Reply, not Quick Reply, and don't attach a picture instead of the document itself).

However, if your data is spread among multiple files, uploading them is impractical. Make a copy of your spreadsheet. Be sure you are working with the copy. Break (Edit → Links) the links to your other spreadsheets. Save the copy after removing the links. Alternatively, select the cells with the external links and use Edit → Copy; Edit → Paste Special → Text/Numbers/Date/Time (but not Formulas). Attach the copy. Indicate exactly which sheet/cells contain the formulas which do not match.
Mr. Programmer
AOO 4.1.7 Build 9800 on MacOS 10.14.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Moderator
 
Posts: 3903
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA


Return to Calc

Who is online

Users browsing this forum: No registered users and 14 guests