Date: · Thread:
2021 Archives by date, by thread · List index

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

```On 2021/12/15 23:45, Michael D. Setzer II wrote:
```
```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.
```
```
```
To Clarify: There are multiple duplicate dates in the first column of Sheet 1. the only difference being the adjacent time in the same field.
```
```
What is needed is to SUM all values for a particular date, regardless of time, either in column B, C, D.
```
```
```
Added a 5th column labled as Date Only and put formula
under it. =int(a2) and copied for the 4 data elements.
```
```
```
Thank you to you and Remy, the first formula on Sheet 2.A1 to strip the time function is INT(Sheet1.a1)
```

```
```
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.
```
```
```
I would prefer not to have to use the sort function. The main issue is amalgamating multiple values on duplicate dates i.e. sum all results in a column B/C/D regardless that there are multiple values are sorted by date and time.
```
```
I am happy to learn about the creation of the date in Sheet 2 from Sheet 1 using INT.
```
```
If the base formulae are there to calculate what is needed, creating a Macro, I think, is overkill.
```
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.
```
```
--
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
```

Context

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.