Re: [libreoffice-users] SUM, SUMIF, IF, INDEX, MATCH, VLOOKUP?

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

Date sent:              Wed, 15 Dec 2021 21:11:55 +0200
To:                     LibreOffice Users 
<users@global.libreoffice.org>
From:                   "Hylton Conacher (ZR1HPC)" 
<hylton@conacher.co.za>
Subject:                [libreoffice-users] SUM, SUMIF, IF, INDEX, 
MATCH, VLOOKUP?

> Hi,
>
> 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.
Data/Subtotals
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 
day?

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: users+unsubscribe@global.libreoffice.org
> Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
> Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
> List archive: https://listarchives.libreoffice.org/global/users/
> Privacy Policy: https://www.documentfoundation.org/privacy


+------------------------------------------------------------+
 Michael D. Setzer II - Computer Science Instructor 
(Retired)     
 mailto:mikes@guam.net                            
 mailto:msetzerii@gmail.com
 Guam - Where America's Day Begins                        
 G4L Disk Imaging Project maintainer 
 http://sourceforge.net/projects/g4l/
+------------------------------------------------------------+




-- 
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy