DirtyDawg
Level 1 (8 points)
iWork

Q: Daily Summaries/Totals from a List of Multiple Entries per Day?

Not really sure it's relevant, but here are the specs on my machine: iMac (27-inch, Mid 2010), 2.93 GHz Intel Core i7, 16 GB 1333 MHz DDR3 RAM, OS X 10.11.6.

 

What may be relevant: Numbers 3.6.2

 

I have a data table like this:

 

               TABLE-1

7/27/2016 6:15 PM            70

7/27/2016 10:00 PM          80

7/28/2016 12:00 AM          90

7/28/2016 4:30 AM            70

7/29/2016 7:00 AM            60

7/29/2016 3:00 PM            70

 

I would like to produce a daily summary which would total only the amounts in the right-hand column for each specific day, like this"

 

               TABLE-2

7/27/2016            150

7/28/2016            160

7/29/2016            130

 

Of course those are grossly abbreviated tables and I could just do it in my head if that were all there is. It seems like this would be simple and widely applicable process, but thus far I have not been able to create a formula that will do it.

 

I have tried using the SUMIFS command using the MONTH and DAY functions to pull only the month and day from the date/time cell, but I get syntax errors so I'm thinking the functions are not admisable in the "condition" argument of SUMIFS. I've tried isolating the month number and the day number in a separate table using the MONTH and DAY functions, then applying SUMIFS but still cannot make it work.

 

I think if I could strip the time from the date, I could find a way to do this fairly easily. But since the time seems inseparable and the time doesn't match between table-1 and table-2, it has become complicated.

 

Any help is appreciated!

 

Thanks,

Scott

iMac, OS X El Capitan (10.11.6)

Posted on Aug 24, 2016 2:31 PM

Close
DirtyDawg

Q: Daily Summaries/Totals from a List of Multiple Entries per Day?

  • All replies
  • Helpful answers

  • by t quinn,

    t quinn t quinn Aug 24, 2016 5:12 PM in response to DirtyDawg
    Level 5 (5,063 points)
    Mac OS X
    Aug 24, 2016 5:12 PM in response to DirtyDawg

    Hi Scott,

     

    Here is an approach using and index created with YEARFRAC().

    Screen Shot 2016-08-24 at 6.09.06 PM.png

    In the report table

    B2 = SUMIF(Table 1::C,"="&YEARFRAC("1/1/2016",A2,1),Table 1::B)

    This is filled down.

    in the data table

    C2= YEARFRAC("1/1/2016",A2,1)

    also filled down.

     

    quinn

  • by SGIII,

    SGIII SGIII Aug 24, 2016 6:36 PM in response to DirtyDawg
    Level 6 (10,796 points)
    Mac OS X
    Aug 24, 2016 6:36 PM in response to DirtyDawg

    Here's an approach using SUMIFS that doesn't require extra columns:

     

    Screen Shot 2016-08-24 at 9.31.29 PM.png

     

    The formula in B2, filled down, is:

     

         =SUMIFS(Table 1::B,Table 1::A,">="&A2,Table 1::A,"<"&A2+1)

     

    A "date" entered in column A of table 2 is actually a date-time string where the time string is 12:00:00 AM.  So to get that day's value test for anything greater than or equal to that but less than that plus 1 day (which is the midnight that starts the following day).

     

    SG