Sumproduct calculation

[marty31]

HI
i have been using the sumproduct calculation "=SUMPRODUCT(--(D977:D1170="OFF")*($C$977:$C$1170))-SUMPRODUCT(--(D977:D1170="ON")*($C$977:$C$1170))" provided to me by a colleague to calculate time that certain points within a system are ON. I now have 3 months worth of data that i would like to use the date column as well to reduce the amount of hours need to do this for every day. is this possible?
Below is snapshot of data
Thanks in advance


4/19/2011 21:06:43 ENABLED
4/19/2011 21:18:03 DISABLED
4/19/2011 21:54:39 ENABLED
4/19/2011 22:04:18 DISABLED
4/19/2011 22:46:13 ENABLED
4/19/2011 22:55:19 DISABLED
4/20/2011 1:00:34 ENABLED
4/20/2011 9:17:58 DISABLED
4/20/2011 9:47:09 ENABLED
4/20/2011 10:34:13 DISABLED
4/20/2011 11:01:38 ENABLED
4/20/2011 14:02:09 DISABLED
4/20/2011 14:26:34 ENABLED
4/20/2011 17:31:13 DISABLED
4/20/2011 17:57:49 ENABLED
4/20/2011 18:19:33 DISABLED
4/20/2011 18:56:43 ENABLED
4/20/2011 19:15:38 DISABLED
4/20/2011 19:47:04 ENABLED
4/20/2011 20:01:14 DISABLED
4/20/2011 20:37:39 ENABLED
4/20/2011 20:49:08 DISABLED
4/20/2011 21:25:53 ENABLED
4/20/2011 21:36:28 DISABLED
4/20/2011 22:15:18 ENABLED
4/20/2011 22:24:24 DISABLED
4/21/2011 0:32:38 ENABLED
4/21/2011 0:40:43 DISABLED
4/21/2011 7:28:48 ENABLED
4/21/2011 7:57:48 DISABLED
4/21/2011 8:29:14 ENABLED
4/21/2011 10:35:18 DISABLED
4/21/2011 11:00:38 ENABLED
4/21/2011 18:02:34 DISABLED
4/21/2011 18:33:38 ENABLED
4/21/2011 18:54:03 DISABLED
4/21/2011 21:05:53 ENABLED
4/21/2011 21:22:08 DISABLED
4/21/2011 21:55:08 ENABLED
4/21/2011 22:09:28 DISABLED
4/21/2011 22:50:43 ENABLED
4/21/2011 22:59:29 DISABLED
4/21/2011 23:00:43 ENABLED
4/21/2011 23:01:29 DISABLED
4/21/2011 23:17:49 ENABLED
4/21/2011 23:27:23 DISABLED
4/22/2011 0:28:24 ENABLED
4/22/2011 0:33:49 DISABLED
4/22/2011 7:16:54 ENABLED
4/22/2011 7:32:18 DISABLED
4/22/2011 8:11:18 ENABLED
4/22/2011 8:23:18 DISABLED
4/22/2011 9:01:59 ENABLED

 

[T. Valko]

HI
i have been using the sumproduct calculation "=SUMPRODUCT(--(D977:D1170="OFF")*($C$977:$C$1170))-SUMPRODUCT(--(D977:D1170="ON")*($C$977:$C$1170))" provided to me by a colleague to calculate time that certain points within a system are ON. I now have 3 months worth of data that i would like to use the date column as well to reduce the amount of hours need to do this for every day. is this possible?
Below is snapshot of data
Thanks in advance


4/19/2011 21:06:43 ENABLED
4/19/2011 21:18:03 DISABLED
4/19/2011 21:54:39 ENABLED
4/19/2011 22:04:18 DISABLED
4/19/2011 22:46:13 ENABLED
4/19/2011 22:55:19 DISABLED
4/20/2011 1:00:34 ENABLED
4/20/2011 9:17:58 DISABLED
4/20/2011 9:47:09 ENABLED
4/20/2011 10:34:13 DISABLED
4/20/2011 11:01:38 ENABLED
4/20/2011 14:02:09 DISABLED
4/20/2011 14:26:34 ENABLED
4/20/2011 17:31:13 DISABLED
4/20/2011 17:57:49 ENABLED
4/20/2011 18:19:33 DISABLED
4/20/2011 18:56:43 ENABLED
4/20/2011 19:15:38 DISABLED
4/20/2011 19:47:04 ENABLED
4/20/2011 20:01:14 DISABLED
4/20/2011 20:37:39 ENABLED
4/20/2011 20:49:08 DISABLED
4/20/2011 21:25:53 ENABLED
4/20/2011 21:36:28 DISABLED
4/20/2011 22:15:18 ENABLED
4/20/2011 22:24:24 DISABLED
4/21/2011 0:32:38 ENABLED
4/21/2011 0:40:43 DISABLED
4/21/2011 7:28:48 ENABLED
4/21/2011 7:57:48 DISABLED
4/21/2011 8:29:14 ENABLED
4/21/2011 10:35:18 DISABLED
4/21/2011 11:00:38 ENABLED
4/21/2011 18:02:34 DISABLED
4/21/2011 18:33:38 ENABLED
4/21/2011 18:54:03 DISABLED
4/21/2011 21:05:53 ENABLED
4/21/2011 21:22:08 DISABLED
4/21/2011 21:55:08 ENABLED
4/21/2011 22:09:28 DISABLED
4/21/2011 22:50:43 ENABLED
4/21/2011 22:59:29 DISABLED
4/21/2011 23:00:43 ENABLED
4/21/2011 23:01:29 DISABLED
4/21/2011 23:17:49 ENABLED
4/21/2011 23:27:23 DISABLED
4/22/2011 0:28:24 ENABLED
4/22/2011 0:33:49 DISABLED
4/22/2011 7:16:54 ENABLED
4/22/2011 7:32:18 DISABLED
4/22/2011 8:11:18 ENABLED
4/22/2011 8:23:18 DISABLED
4/22/2011 9:01:59 ENABLED

It's not real clear what you're asking.

However, you can use a more efficient formula than the one you're currently using.

=SUMIF(D977:D1170,"OFF",$C$977:$C$1170)-SUMIF(D977:D1170,"ON",$C$977:$C$1170)

 

[marty31]

Thanks for that one, cheers
Sorry for the lack of description,
Currently i filter column A by the date and calculate result in 1 cell. i do this for the entire month... very time consuming, wondering if the date column could be used to calculate the monthly total?

 

[T. Valko]

Thanks for that one, cheers
Sorry for the lack of description,
Currently i filter column A by the date and calculate result in 1 cell. i do this for the entire month... very time consuming, wondering if the date column could be used to calculate the monthly total?

OK, what version of Excel are you using?

 

[marty31]

Apologies

Excel 2003

 

[T. Valko]

Apologies

Excel 2003

Ok, then we have to go back to using the SUMPRODUCT version...

Using your posted formula as the template...

=SUMPRODUCT(--(D977:D1170="OFF")*($C$977:$C$1170))-SUMPRODUCT(--(D977:D1170="ON")*($C$977:$C$1170))

Enter the 1st of the month date for the month in question in some cell. For example, to get the sum for April 2011:

A1 = 4/1/2011

Let's assume your dates are in column B.

Then the formula becomes:

=SUMPRODUCT(--(B977:B1170-DAY(B977:B1170)+1=A1),--(D977:D1170="OFF"),$C$977:$C$1170)-SUMPRODUCT(--(B977:B1170-DAY(B977:B1170)+1=A1),--(D977:D1170="ON"),$C$977:$C$1170)