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