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)" <hyl...@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+unsubscr...@global.libreoffice.org > Problems? > http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ > Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette > List archive: http://listarchives.libreoffice.org/global/users/ > Privacy Policy: http://www.documentfoundation.org/privacy +------------------------------------------------------------+ Michael D. Setzer II - Computer Science Instructor (Retired) mailto:mi...@guam.net mailto:msetze...@gmail.com Guam - Where America's Day Begins G4L Disk Imaging Project maintainer http://sourceforge.net/projects/g4l/ +------------------------------------------------------------+ -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ Privacy Policy: http://www.documentfoundation.org/privacy
