2021 Archives by date, by thread · List index

# 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/
```
```

+------------------------------------------------------------+
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/