Date: prev next · Thread: first prev next last
2021 Archives by date, by thread · List index

On 15 Dec 2021 at 21:11, Hylton Conacher (ZR1HPC) 

Date sent:              Wed, 15 Dec 2021 21:11:55 +0200
To:                     LibreOffice Users 
From:                   "Hylton Conacher (ZR1HPC)" 
Subject:                [libreoffice-users] SUM, SUMIF, IF, INDEX, 


I am quickly losing patience with a task that requires a sum of values 
that occur on the same date, but at different times.

My raw data sheet is in 4 columns
Date/Time | Value 1| Value Y| Value z
2021-10-08 08:00:00| 5| 7| 0.15
2021-10-08 14:05:16| 3| 10| 5
2021-10-09 10:05:30| 15| 3| 25
2021-10-09 18:00:00| 15| 9| 6

Don't know if this is what you are wanting, but I did this.
Copied the above data into a sheet using shift-ctrl-V use | 
as delimiter. That date and time is pasted as a date/time 
value versus a text string.

Added a 5th column labled as Date Only and put formula 
under it. =int(a2) and copied for the 4 data elements.

Then highlighted the 5 rows of Data.
Group By "Date Only"
Under Calc Subtotals for: Checked value Y and Date Only
For value Y use SUM function
For Date Only used AVG function
Then OK.
That has it insert rows for each group with the totals for 
each day. It also shows a final total of everything that 
could be ignored.

Posible extra step if you just want the subtotal data.
In cells F2 and G2 put the formulas
=if(a2=0,e2,"")  That gets date only for subtotal lines
=if(a2=0,c2,"")  That gets value y for subtotal lines.
Copy F2:g2 down to F7: (don't need row 8 for final total).
Then highlight F2:G7 use Ctrl-C
Go to H2 - Do ctrl-shift-V and paste text number date/time
The Click Data - Sort ascending. 
That gives just the subtotal lines with date and total of 

Might be a better way to do it, seems to work. Perhaps a 
macro to automate the whole thing.

I have a secondary sheet with just the date i.e. 2010-10-08, acquired 
with formula `=LEFT($Data.A4,10)'. Column B of this sheet needs to be 
the SUM of all the numbers in the 3rd column, BUT only for the date in 
column A i.e.

2010-10-08| 17
2010-10-09| 12

I have tried many Google searches and seen many videos on how this is 
supposed to work yet for some reason, being race, colour, creed, or 
stupidity, I cannot get the result I seek. It would seem that the 
summation of data with the same date, but different time component in 
the main data sheet is causing an issue.

I'd really appreciate some pointers here, even if not possible. If not 
possible what could be done. I just don't understand the 
INDEX(MATCH())/vlookup  argument.

To unsubscribe e-mail to:
Posting guidelines + more:
List archive:
Privacy Policy:

 Michael D. Setzer II - Computer Science Instructor 
 Guam - Where America's Day Begins                        
 G4L Disk Imaging Project maintainer

To unsubscribe e-mail to:
Posting guidelines + more:
List archive:
Privacy Policy:


Privacy Policy | Impressum (Legal Info) | Copyright information: Unless otherwise specified, all text and images on this website are licensed under the Creative Commons Attribution-Share Alike 3.0 License. This does not include the source code of LibreOffice, which is licensed under the Mozilla Public License (MPLv2). "LibreOffice" and "The Document Foundation" are registered trademarks of their corresponding registered owners or are in actual use as trademarks in one or more countries. Their respective logos and icons are also subject to international copyright laws. Use thereof is explained in our trademark policy.