SUM, SUMIF, IF, INDEX, MATCH, VLOOKUP?

users
#1

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
...
..
.

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.

#2

Hi,

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
...

[...]

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.

A date-time is a floating number. The integer part being the date (from a reference day -- 12/30/1899 is a standard), the decimal part being the time (0.0 for OO:OO to 0.9999999... for 23:59:59...)
Thus it's no suprise that SUMIF and its siblings won't find a date.

-> Did you try querying on the INT() value of the date-time, which is the date part?

Best,

#3

...
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 [spaced for comprehension]
Date/Time [stamp] | 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
...
..
.

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
...
..
.

Just trying to understand your question properly, so for clarification:
  (1a) Your two sheets differ in column A, in that the first has a date/time stamp, the second date only (extracted from the first)?
  (1b) Does each row of the second sheet reflect the date of the corresponding row in the first sheet (in other words, dates are repeated on the second sheet, as suggested by your formula ($Data.A4, $Data.A5, $Data.A6, ...), or is each date on the second sheet listed just once?
  (2) We don't need to worry about columns B or D of the first sheet, just column C? [Any significance to columns B-C-D being indexed in three different formats (Numeric-UPPERCASE-lowercase)?]

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.

  (3a) Did you try confirming that conjecture using a test example with just the date, not a date/time stamp?
  (3b) How is that date/time field acquired? Could it be acquired/input as two separate fields, date and time?

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.

  (4) [This is related to questions 1 and 3] Given that you acquire the date for your second sheet's column A as a text field, is there a possible data type mismatch?

#4

Date sent: Wed, 15 Dec 2021 21:11:55 +0200
<users@global.libreoffice.org>
<hylton@conacher.co.za>
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.

#5

=The subtotal seems to cause issues, so another option.
Again in Column E:
E1: Date Only
E2:Ex: =int(a2) copy down
F2 - Formula $5 needs to be changed to last row
=IF(E2E3,SUMIFS($C$2:$C$5,$E$2:$E$5,=uot;INT($A$2:$A$5)),)
Copy down Column F
G2 - Same as F
=IF(E2E3,E2,)
Copy down Column G

Data is there, but blank cells.
Can use Ctrl-Shift-V to copy to other location and then do
a data sort ascending to get ridof blank lines.

#6

Hi,

You can do this with an array formula. Before we start:
- The date/time values in column A are actual date values, not text
values - you will need to convert the information in Data.A4 to a real
date
The formula you will then want is this one:

=SUMIF(INT(Sheet1.A$2:Sheet1.A$5),CONCAT("=";Sheet2.A2),Sheet1.B$2:Shee
t1.B$5)

Once you have entered the formula, terminate with CTRL-SHIFT-Enter to
make it an array formula. It will show like this in the formula bar
(the curly braces are what you are looking for):

{=SUMIF(INT(Sheet1.A$2:Sheet1.A$5),CONCAT("=";Sheet2.A2),Sheet1.B$2:She
et1.B$5)}

Since the dates you have are real dates, they are actually numerical
values where the integer portion represents the year. The INT() of the
date range will extract that number and will be compared with the date
you extracted (BTW, I suggest you use INT($Data.A4) rather than the
character extraction you used).

This works well with your data sample.

I hope this helps.
Rémy.

#7

Reply without the other stuff since this is somewhat long.
Wanted to try to create a macrothat would do everthing
with just the original data in columns A-D.

With old Quattro pro would have been amuch simpiler
process.. Would be nice if we could upload sheets.

Well, just wanted to see if I could do something.

The LibreOffice Macro recorder is terrible. So had to do a
lot. Seems to work, but not pretty. Found that something
that use to work no longer does. Have an old macro that
actual used a regular sort with options, but now recorder
just makes a single rem line that does nothing. Does work
with a just sort ascending record, so had to switch data
and total columns.
With column A having the Date and Time value
and Columns B-D having the values.
Macro will do the Integer Date Only in Column E.
Puts the Date in Column F with the last line for a date
Puts the Total in Column G for that data.
Copies the data from F and G to H and I without blank
lines and sorts it. If data was already sorted that line
might not be needed?? It also sets the formatting.

I'm not following the latest message that refers to a
Sheet2? Will have to look at it more.

Saved macro as
sub subtotal
rem ----------------------------------------------------------------------
rem define variables
dim documentas object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document= ThisComponent.CurrentController.Frame
dispatcher = createUnoService(com.sun.star.frame.DispatchHelper)

rem ----------------------------------------------------------------------
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name =ToPoint
args1(0).Value =$E$1:$H$50

dispatcher.executeDispatch(document,.uno:GoToCell, 0, args1())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document,.uno:Cut, 0, Array())

rem ----------------------------------------------------------------------
dim args2(0) as new com.sun.star.beans.PropertyValue
args2(0).Name =ToPoint
args2(0).Value =$E$1

dispatcher.executeDispatch(document,.uno:GoToCell, 0, args2())

rem ----------------------------------------------------------------------
dim args3(0) as new com.sun.star.beans.PropertyValue
args3(0).Name =StringName
args3(0).Value =Date Only
dispatcher.executeDispatch(document,.uno:EnterString, 0, args3())

dim args4(0) as new com.sun.star.beans.PropertyValue
args4(0).Name =ToPoint
args4(0).Value =$E$2

dispatcher.executeDispatch(document,.uno:GoToCell, 0, args4())

rem ----------------------------------------------------------------------
dim args5(0) as new com.sun.star.beans.PropertyValue
args5(0).Name =StringName
args5(0).Value ==if(a20,int(a2),+chr$(34)+chr$(34)+)
dispatcher.executeDispatch(document,.uno:EnterString, 0, args5())

dim args6(0) as new com.sun.star.beans.PropertyValue
args6(0).Name =ToPoint
args6(0).Value =$F$2

dispatcher.executeDispatch(document,.uno:GoToCell, 0, args6())

dim args7(0) as new com.sun.star.beans.PropertyValue
args7(0).Name =StringName
args7(0).Value ==IF(E2E3,E2,+CHR$(34)+CHR$(34)+)

dispatcher.executeDispatch(document,.uno:EnterString, 0, args7())

dim args8(0) as new com.sun.star.beans.PropertyValue
args8(0).Name =ToPoint
args8(0).Value =$G$2

dispatcher.executeDispatch(document,.uno:GoToCell, 0, args8())

rem ----------------------------------------------------------------------
dim args9(0) as new com.sun.star.beans.PropertyValue
args9(0).Name =StringName
args9(0).Value ==IF(E2E3,SUMIFS($C$2:$C$50,$E$2:$e$50,+CHR$(34)+=+CHR$(34)+INT($A$2:$A$50)),+CHR$(34)+CHR$(34)+)

dispatcher.executeDispatch(document,.uno:EnterString, 0, args9())

dim args10(0) as new com.sun.star.beans.PropertyValue
args10(0).Name =ToPoint
args10(0).Value =$E$2:$G$2

dispatcher.executeDispatch(document,.uno:GoToCell, 0, args10())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document,.uno:Copy, 0, Array())

dim args11(0) as new com.sun.star.beans.PropertyValue
args11(0).Name =ToPoint
args11(0).Value =$E$3:$G$50

dispatcher.executeDispatch(document,.uno:GoToCell, 0, args11())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document,.uno:Paste, 0, Array())

dim args12(0) as new com.sun.star.beans.PropertyValue
args12(0).Name =ToPoint
args12(0).Value =$E$2:$F$50
dispatcher.executeDispatch(document,.uno:GoToCell, 0, args12())

rem ----------------------------------------------------------------------
dim args13(0) as new com.sun.star.beans.PropertyValue
args13(0).Name =NumberFormatValue
args13(0).Value = 84

dispatcher.executeDispatch(document,.uno:NumberFormatValue, 0,
args13())

rem ----------------------------------------------------------------------
rem Raw Data downloaded and pasted from
https://www.worldometers.info/coronavirus/country/us/
dim args14(0) as new com.sun.star.beans.PropertyValue
args14(0).Name =ToPoint
args14(0).Value =$F$2:$G$60
dispatcher.executeDispatch(document,.uno:GoToCell, 0, args14())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document,.uno:Copy, 0, Array())

rem Raw Data downloaded and pasted from
https://www.worldometers.info/coronavirus/country/us/
dim args15(0) as new com.sun.star.beans.PropertyValue
args15(0).Name =ToPoint
args15(0).Value =$H$2
dispatcher.executeDispatch(document,.uno:GoToCell, 0, args15())

rem ----------------------------------------------------------------------
dim args16(5) as new com.sun.star.beans.PropertyValue
args16(0).Name =Flags
args16(0).Value =SVD
args16(1).Name =FormulaCommand
args16(1).Value = 0
args16(2).Name =SkipEmptyCells
args16(2).Value = false
args16(3).Name =Transpose
args16(3).Value = false
args16(4).Name =AsLink
args16(4).Value = false
args16(5).Name =MoveMode
args16(5).Value = 4

dispatcher.executeDispatch(document,.uno:InsertContents, 0, args16())
rem Raw Data downloaded and pasted from
https://www.worldometers.info/coronavirus/country/us/
dim args17(0) as new com.sun.star.beans.PropertyValue
args17(0).Name =ToPoint
args17(0).Value =$H$2:$I$50
dispatcher.executeDispatch(document,.uno:GoToCell, 0, args17())

dispatcher.executeDispatch(document,.uno:SortAscending, 0, Array())

dim args18(0) as new com.sun.star.beans.PropertyValue
args18(0).Name =ToPoint
args18(0).Value =$H$2:$H$50
dispatcher.executeDispatch(document,.uno:GoToCell, 0, args18())

rem ----------------------------------------------------------------------
dim args19(0) as new com.sun.star.beans.PropertyValue
args19(0).Name =NumberFormatValue
args19(0).Value = 84

dispatcher.executeDispatch(document,.uno:NumberFormatValue, 0,
args19())

end sub

Is setup to handle up to data in A2 to A50, but could extend macro to do bigger
range easily.

Data as displayed - Added a few more lines of data.
Date/Time , Value 1, Value Y, Value z,Date Only,
2021-10-08 08:00:00,5,7,0.15,2021-10-08,2021-10-08,17
2021-10-08 14:05:16,3,10,5,2021-10-08,2021-10-08,17,2021-10-09,12
2021-10-09 10:05:30,15,3,25,2021-10-09,2021-10-13,22
2021-10-09 18:00:00,15,9,6,2021-10-09,2021-10-09,12,2021-10-14,99
2021-10-13 08:00:00,5,7,0.15,2021-10-13,
2021-10-13 14:05:16,3,15,5,2021-10-13,2021-10-13,22,
2021-10-14 10:05:30,15,87,25,2021-10-14,
2021-10-14 18:00:00,15,12,6,2021-10-14,2021-10-14,99,

Data with formulas
Date/Time , Value 1, Value Y, Value z,Date Only,
2021-10-08
08:00:00,5,7,0.15,=IF(A20,INT(A2),),=IF(E2E3,E2,),=IF(E2E3,SUMIFS($C$2:$C$50,$E$2:$E$50,=INT($A$2:$A$50)),),2021-10-08,17
2021-10-08
14:05:16,3,10,5,=IF(A30,INT(A3),),=IF(E3E4,E3,),=IF(E3E4,SUMIFS($C$2:$C$50,$E$2:$E$50,=INT($A$2:$A$50)),),2021-10-09,12
2021-10-09
10:05:30,15,3,25,=IF(A40,INT(A4),),=IF(E4E5,E4,),=IF(E4E5,SUMIFS($C$2:$C$50,$E$2:$E$50,=INT($A$2:$A$50)),),2021-10-13,22
2021-10-09
18:00:00,15,9,6,=IF(A50,INT(A5),),=IF(E5E6,E5,),=IF(E5E6,SUMIFS($C$2:$C$50,$E$2:$E$50,=INT($A$2:$A$50)),),2021-10-14,99
2021-10-13
08:00:00,5,7,0.15,=IF(A60,INT(A6),),=IF(E6E7,E6,),=IF(E6E7,SUMIFS($C$2:$C$50,$E$2:$E$50,=INT($A$2:$A$50)),),
2021-10-13
14:05:16,3,15,5,=IF(A70,INT(A7),),=IF(E7E8,E7,),=IF(E7E8,SUMIFS($C$2:$C$50,$E$2:$E$50,=INT($A$2:$A$50)),),
2021-10-14
10:05:30,15,87,25,=IF(A80,INT(A8),),=IF(E8E9,E8,),=IF(E8E9,SUMIFS($C$2:$C$50,$E$2:$E$50,=INT($A$2:$A$50)),),
2021-10-14
18:00:00,15,12,6,=IF(A90,INT(A9),),=IF(E9E10,E9,),=IF(E9E10,SUMIFS($C$2:$C$50,$E$2:$E$50,=INT($A$2:$A$50)),),

#8

...

>> 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 [spaced for comprehension]
>> Date/Time [stamp] | 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
>> ...
>> ..
>> .
>>
>> 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
>> ...
>> ..
>> .
>
> Just trying to understand your question properly, so for clarification:
> (1a) Your two sheets differ in column A, in that the first has a date/time stamp, the second date only (extracted from the first)?

Correct

> (1b) Does each row of the second sheet reflect the date of the corresponding row in the first sheet (in other words, dates are repeated on the second sheet, as suggested by your formula ($Data.A4, $Data.A5, $Data.A6, ...), or is each date on the second sheet listed just once?

No, the second sheet merely has a single entry per date. I basically used LEFT(1st sheet date/time column, 10) then manually typed the following date in the cell below, clicked on bottom right of cell and dragged down to current date.

> (2) We don't need to worry about columns B or D of the first sheet, just column C? [Any significance to columns B-C-D being indexed in three different formats (Numeric-UPPERCASE-lowercase)?]

Yes, do worry about columns B, D. I would like to do something similar to C, but figured I might try and bastardise the formula for C, for B +D.

<snip>
> (3a) Did you try confirming that conjecture using a test example with just the date, not a date/time stamp?

Yes

> (3b) How is that date/time field acquired? Could it be acquired/input as two separate fields, date and time?
It is a combined field downloaded in a .csv file.

<snip>

> (4) [This is related to questions 1 and 3] Given that you acquire the date for your second sheet's column A as a text field, is there a possible data type mismatch?
The date column in both sheets is formatted as a date column
  Sheet 1: yyyy-mm-dd hh:mm:ss
  Sheet 2: yyyy-mm-dd

#9

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.

#10

After doing much reading of examples I thought the below formula would work, alas it does NOT WORK. Where is it wrong?

I copied the data I gave above onto a blank spreadsheet, formatted the first column as date YYY-MM-DD HH:MM:SS. I entered the below formula in column G1

=SUM(INDEX($A$2:$D$5,0,MATCH(A2,$B1:$D1,0)))

The MATCH i.e. A2 is proving troublesome as I have tried the following:
1. INT(A2) formula result #N/A
2. 2021-10-8 The formula will not accept 08 formula result #N/A
3. LEFT(A2) although this gives me a function result of "44477.3333" and formula result #N/A

So how the _ _ _ _ do you sum values for a particular column for multiple occurrences of the same date as above oh learned friends?

#11

------------------------------

=IF(LEFT(A2,10)=("2021-10-08"),B2,"")

------------------------------

If you paste that formula into column E, you should have nulls where the
date does not match and the value in Column B where the date matches.

Note the above suggestion requires column A left in TEXT format. If you
import your data as or convert it to a DATE VALUE, then "-" (equals) won't
work if you have hours/minutes/seconds included in column A (since the date
has no fractional value but unless you record something at midnight the
values in column A will all have fractional values (the HHMMSS represented
as a fractional portion of a day.) If you're dealing with DATE VALUEs You'd
need to do both a < and >= embedded in parenthesis to capture all values
within the day:

---------------------------------
IF (A2<10/8/21,IF(A2>=10/9/21,B2,""),"")
---------------------------------

I validated the first example works... I haven't validated the 2nd, but the
methodology is sound, as long as my spelling and first attempt at the
specific logic are accurate.

#12

I've never tried it, but the ABS function on the Date Cell might also
work.

#13

and my 1st attempt at logic is horribly flawed for the DATE VALUE cells:

<, >= should be

#14

And you can tell the difference between a 'text' date and a 'date value'
date by turning on "value highlighting" menu-->View-->Value Highlighting

Blue is a value or date value,

Green is a formula,

Black (or the regularly formatted color if you've painted your spreadsheet)
is text.