# How do I `SUM` by multiple columns in Excel

I have a comma delimited file that includes two columns date/time (which imports as Excel's `mm/dd/yyyy hh:mm` custom format) and status of 1 or 0. The status represents a piece of equipment either being on or off. I'm trying to generate a graph that will show, hours up vs. down by day.

CONSIDER:

``````1/1/2012 00:00, 1
1/1/2012 03:00, 0
1/1/2012 14:00, 1
1/3/2012 00:00, 0
``````

This tells me that the equipment was up for three hours, down for eleven hours, and then up for thirty-four hours (across two calendar days). However, I would like to generate a graph that shows how many hours PER DAY we were up or down.

CONSIDER:

``````1/1 XXXXXXXXXXXXX-----------   (up 13, down 11)
1/2 XXXXXXXXXXXXXXXXXXXXXXXX   (up 24)
``````

To me, it seems that I need to generate a dataset summing HOURS by STATUS by CALENDAR DAY...but I can't seem to find a flavor of pivot table or nested `SUM(IF(SUMIF(...)))` combination to make it work.

Most troubling is accounting for date changes...in my example above, since my uptime starting at 14:00 on 1/1/2012 crosses midnight, I need to know that 10 uptime hours get totalled with 1/1/2012 and 24 uptime hours get totalled with 1/2/2012.

I may be able to do something with a calendar list to drive the date summation, but then I need a way to compare `01/01/2012` to `01/01/2012 03:00` as equal. There's got to be a way along the lines of `if(INTEGER-PORTIONS-OF-SERIAL-DATES-ARE-EQUAL,TOTAL-HOURS-IF-VALUE-IS_1,0)` but nothing's worked so far.

Any suggestions? I've been battling this most of the day, and need a fresh perspective.

Thanks

-

OMGBBQ this is challenging! I wish I could explain this better, but please bear with me.

My solution requires some additional real estate -- 3 columns to be exact -- and is based on a few assumptions:

1. The time stamp data would have alternating rows of 1's and 0's, emulating an on/off switch like you described (See Image 1 below).
2. Time stamps are arranged from oldest to newest.

For simplicity's sake, I'm using whole hours. Though you can add minute-level time stamps if you prefer.

Column E tracks the number of hours left for the day at each time stamp. I used this to "SPLIT" elapsed times that cross midnight.

## ON/OFF Columns

Here's the formula in the ON column:

``````=SUM(IF(AND(\$B3,A4<>"",INT(\$A4)=INT(\$A3)),(\$A4-\$A3)*24,0),
IFERROR(IF(AND(\$B3=1,INT(\$A3)<>INT(\$A4)),\$E3,IF(AND(\$B2=1,INT(\$A3)<>INT(\$A2)),24-\$E3,0)),0))
``````

1. The number of hours the switch is ON if the entry isn't the last time stamp for the day; otherwise, 0.

2. The number of hours left until midnight if this is the last time stamp for the day OR the number of hours elapsed since midnight if this is the first time stamp for the day.

The formula in the OFF column is similar, except it checks if Status = 0.

``````=SUM(IF(AND(NOT(\$B3),B4<>"",INT(\$A4)=INT(\$A3)),(\$A4-\$A3)*24,0),
IFERROR(IF(AND(\$B3=0,INT(\$A3)<>INT(\$A4)),\$E3,IF(AND(\$B2=0,INT(\$A3)<>INT(\$A2)),24-\$E3,0)),\$B3*(\$A3-INT(\$A3))*24))
``````

To extract the DATE from the time stamp column, I used the `INT()` function. Excel uses integers to represent dates and decimals to represent times (a fraction of a day or of 24 hours). For example, `06/01/2012 18:00` is equal to `41061.75` (18 hours past midnight is 18/24 which is 0.75). I believe this answers your last paragraph.

## Consolidating the Hours

Here's the formula in the Hours ON column (See Image Below). It's an array formula, so you need to enter it using Ctrl+Shift+Enter and then copy it down:

``````=SUM((INT(stamps)=\$G3)*hours_on)
``````

In the Hours OFF column:

``````=SUM((INT(stamps)=\$G3)*hours_off)
``````

where
`stamps` is a Named Range refering to the Time Stamp range (Column A in my example)
`hours_on` is a Named Range referring to the ON range (Column C)
`hours off` is a Named Range referring to the OFF range (Column D)

## Chart

Notice how for each date, the number of ON and OFF hours sum up to 24.

If you'd like to study the formulas and the workbook, here's a copy: http://db.tt/KZgH7SFV

-

Assuming you have time stamps in A3:A24 and status in B3:B24 as per Kaze's suggestion then you can get the [decimal] hours `ON` for a date in D3 with this formula

`=(LOOKUP(D3+1,A\$3:B\$24)-SUMPRODUCT((INT(A\$3:A\$24)=D3)+0,MOD(A\$3:A\$24,1),B\$3:B\$24*2-1))*24`

no "helper" columns required....

-

It's not pretty, but here's a solution:

``````       A       B    C  D
---------------------
1 | 1/1/2012 00:00, 1, 1
2 | 1/1/2012 03:00, 0, 1
3 | 1/1/2012 14:00, 1, 1
4 | 1/3/2012 00:00, 0, 3
...
99| 1/31/2012 11:23, 1,31
``````

Then you can make sum columns defined as

``````sumif(D1:D99, 1, C1:C99)
``````

Which, in the case above, will show you the sum of the 1's only when column D (previously defined as the "Day" portion of the datetime, is 1. Repeat that formula for each day of the month (the part that I'm not so crazy about), and you should have your values.

Sum of hours on the 1st of the month

``````sumif(D1:D99, 1, C1:C99)
``````

Sum of hours on the 2nd of the month

``````sumif(D1:D99, 2, C1:C99)
``````

Sum of hours on the 3rd of the month

``````sumif(D1:D99, 3, C1:C99)
``````

and so on...

-
I've considered that, but I can't find a formulaic way to cross the date line...in this case, splitting the 34:00 hours beginning on 1/1/12 into 10:00 hours on 1/1/12 and 24:00 hours on 1/2/2012. I could insert additional rows to break similar data at midnight and add a new record, but that kinda defeats the purpose of the automated log. :) – dwwilson66 Jun 1 '12 at 14:32
I'm pretty sure my solution handles the date line issue. Sumif() checks D1:D99 for a certain value (the day portion of the date) and if it resolves to true it sums the appropriate fields. – nageeb Jun 1 '12 at 15:57