Conditional Sumproduct() Based on the Time of the Day

I was wondering how I can get a conditional Sumproduct to know what time of 
day it is and sum only the columns that meet the following criteria.
  
1)  If it is before 11AM then all the "< Noon-1" Columns for each unit is 
added together in the "totals" column for each row of areas.
2)  If it is after 11 but before 1PM then the "totals" column adds all the 
"Noon" Columns
3)  If it is after 1PM then the "totals" column adds all the "> Noon+1" 
Columns

There can be as many as 300 Units per month and every month has it's own 
worksheet and there are no more then 22 Areas at all times.

I hope the provided layout below shows properly in this post... anyways...
______________________________________________________________________________
   A   |   B     |     C   |    D    |    E    |    F    |    G    |    H    |
_______|_________|_________|_________|_________|_________|_________|_________|
       |         |                             |                             |
       |         |          Unit # 16          |          Unit # 17          |
       | Totals  | < Noon-1|  Noon   | > Noon+1| < Noon-1|  Noon   | > Noon+1|
_______|_________|_________|_________|_________|_________|_________|_________|
Area 01|         |         |         |         |         |         |         |
Area 02|         |         |         |         |         |         |         |
Area 03|         |         |         |         |         |         |         |
Area 04|         |         |         |         |         |         |         |
------------------------------------------------------------------------------






Thanks in Advance,
Rob
0
Utf
1/28/2010 1:14:09 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

3 Replies
715 Views

Similar Articles

[PageSpeed] 31

Try

=SUMPRODUCT((MOD(COLUMN(C3:K3),3)=IF(HOUR(MOD(NOW(),1))<11,0,IF(HOUR(MOD(NOW(),1))<1,1,2)))*(C4:K7))

HTH

Bob

"Rob" <Rob@discussions.microsoft.com> wrote in message 
news:31FCA4CF-7CD7-413A-952E-ADFBF16C25EF@microsoft.com...
>I was wondering how I can get a conditional Sumproduct to know what time of
> day it is and sum only the columns that meet the following criteria.
>
> 1)  If it is before 11AM then all the "< Noon-1" Columns for each unit is
> added together in the "totals" column for each row of areas.
> 2)  If it is after 11 but before 1PM then the "totals" column adds all the
> "Noon" Columns
> 3)  If it is after 1PM then the "totals" column adds all the "> Noon+1"
> Columns
>
> There can be as many as 300 Units per month and every month has it's own
> worksheet and there are no more then 22 Areas at all times.
>
> I hope the provided layout below shows properly in this post... anyways...
> ______________________________________________________________________________
>   A   |   B     |     C   |    D    |    E    |    F    |    G    |    H 
> |
> _______|_________|_________|_________|_________|_________|_________|_________|
>       |         |                             | 
> |
>       |         |          Unit # 16          |          Unit # 17 
> |
>       | Totals  | < Noon-1|  Noon   | > Noon+1| < Noon-1|  Noon   | > 
> Noon+1|
> _______|_________|_________|_________|_________|_________|_________|_________|
> Area 01|         |         |         |         |         |         | 
> |
> Area 02|         |         |         |         |         |         | 
> |
> Area 03|         |         |         |         |         |         | 
> |
> Area 04|         |         |         |         |         |         | 
> |
> ------------------------------------------------------------------------------
>
>
>
>
>
>
> Thanks in Advance,
> Rob 


0
Bob
1/28/2010 4:31:45 PM
Hi,

I am not sure but as far as I know, the formula will not auto refresh.  A 
refresh would have to be forced via F2 enter or F9.  Am I right?

-- 
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Bob Phillips" <bob.phillips@somewhere.com> wrote in message 
news:ekluldDoKHA.1552@TK2MSFTNGP04.phx.gbl...
> Try
>
> =SUMPRODUCT((MOD(COLUMN(C3:K3),3)=IF(HOUR(MOD(NOW(),1))<11,0,IF(HOUR(MOD(NOW(),1))<1,1,2)))*(C4:K7))
>
> HTH
>
> Bob
>
> "Rob" <Rob@discussions.microsoft.com> wrote in message 
> news:31FCA4CF-7CD7-413A-952E-ADFBF16C25EF@microsoft.com...
>>I was wondering how I can get a conditional Sumproduct to know what time 
>>of
>> day it is and sum only the columns that meet the following criteria.
>>
>> 1)  If it is before 11AM then all the "< Noon-1" Columns for each unit is
>> added together in the "totals" column for each row of areas.
>> 2)  If it is after 11 but before 1PM then the "totals" column adds all 
>> the
>> "Noon" Columns
>> 3)  If it is after 1PM then the "totals" column adds all the "> Noon+1"
>> Columns
>>
>> There can be as many as 300 Units per month and every month has it's own
>> worksheet and there are no more then 22 Areas at all times.
>>
>> I hope the provided layout below shows properly in this post... 
>> anyways...
>> ______________________________________________________________________________
>>   A   |   B     |     C   |    D    |    E    |    F    |    G    |    H 
>> |
>> _______|_________|_________|_________|_________|_________|_________|_________|
>>       |         |                             | |
>>       |         |          Unit # 16          |          Unit # 17 |
>>       | Totals  | < Noon-1|  Noon   | > Noon+1| < Noon-1|  Noon   | > 
>> Noon+1|
>> _______|_________|_________|_________|_________|_________|_________|_________|
>> Area 01|         |         |         |         |         |         | |
>> Area 02|         |         |         |         |         |         | |
>> Area 03|         |         |         |         |         |         | |
>> Area 04|         |         |         |         |         |         | |
>> ------------------------------------------------------------------------------
>>
>>
>>
>>
>>
>>
>> Thanks in Advance,
>> Rob
>
> 
0
Ashish
1/28/2010 11:40:32 PM
It will refresh if anything in C3:K7, or any cells in their dependency 
cycle, change, just like any other formula.

HTH

Bob

"Ashish Mathur" <mathurashish@hotmail.com> wrote in message 
news:E26B1DA6-14B4-415C-8913-0D0135D25AAE@microsoft.com...
> Hi,
>
> I am not sure but as far as I know, the formula will not auto refresh.  A 
> refresh would have to be forced via F2 enter or F9.  Am I right?
>
> -- 
> Regards,
>
> Ashish Mathur
> Microsoft Excel MVP
> www.ashishmathur.com
>
> "Bob Phillips" <bob.phillips@somewhere.com> wrote in message 
> news:ekluldDoKHA.1552@TK2MSFTNGP04.phx.gbl...
>> Try
>>
>> =SUMPRODUCT((MOD(COLUMN(C3:K3),3)=IF(HOUR(MOD(NOW(),1))<11,0,IF(HOUR(MOD(NOW(),1))<1,1,2)))*(C4:K7))
>>
>> HTH
>>
>> Bob
>>
>> "Rob" <Rob@discussions.microsoft.com> wrote in message 
>> news:31FCA4CF-7CD7-413A-952E-ADFBF16C25EF@microsoft.com...
>>>I was wondering how I can get a conditional Sumproduct to know what time 
>>>of
>>> day it is and sum only the columns that meet the following criteria.
>>>
>>> 1)  If it is before 11AM then all the "< Noon-1" Columns for each unit 
>>> is
>>> added together in the "totals" column for each row of areas.
>>> 2)  If it is after 11 but before 1PM then the "totals" column adds all 
>>> the
>>> "Noon" Columns
>>> 3)  If it is after 1PM then the "totals" column adds all the "> Noon+1"
>>> Columns
>>>
>>> There can be as many as 300 Units per month and every month has it's own
>>> worksheet and there are no more then 22 Areas at all times.
>>>
>>> I hope the provided layout below shows properly in this post... 
>>> anyways...
>>> ______________________________________________________________________________
>>>   A   |   B     |     C   |    D    |    E    |    F    |    G    |    H 
>>> |
>>> _______|_________|_________|_________|_________|_________|_________|_________|
>>>       |         |                             | |
>>>       |         |          Unit # 16          |          Unit # 17 |
>>>       | Totals  | < Noon-1|  Noon   | > Noon+1| < Noon-1|  Noon   | > 
>>> Noon+1|
>>> _______|_________|_________|_________|_________|_________|_________|_________|
>>> Area 01|         |         |         |         |         |         | |
>>> Area 02|         |         |         |         |         |         | |
>>> Area 03|         |         |         |         |         |         | |
>>> Area 04|         |         |         |         |         |         | |
>>> ------------------------------------------------------------------------------
>>>
>>>
>>>
>>>
>>>
>>>
>>> Thanks in Advance,
>>> Rob
>>
>> 


0
Bob
1/29/2010 12:23:08 AM
Reply:

Similar Artilces:

Writing to web-based database
I am trying to build VBA code that will write to a database locatedona web server. Database is MS Access now, but could use SQL Server orsomething else SQL based. How can I automate the process of opening,appending, and closing a database when the Office application is onthe local computer but the database is on a web server?...

Insert query using date as where condition
I have an insert query that runs every day at midnight that records our client's current environmental status (it's for a mental health facility). Here's the code: ----------------------------------------------------------------------------------------------- INSERT INTO dbo.tblCensusHistoryWithDischarge (FullName, LastName, FirstName, FileNumber, ClientNumber, StaffID, Environment, Location, Residence, FundSource, Act10, StartDate, ExpEndDate, EndDate, CensusTimestamp) SELECT TOP (100) PERCENT dbo.tblClients.LastName + N', ' + dbo.tblClient...

Condition Format #1
I have enclosed a chart with this explanation below. I am needing automate this chart according to the following rules. See chart also, if necessary. Thank you. First Whenever, a 3 digit numeric combination is entered into B, C and D cells and if each of the 3 digits are different (ie.3,7,2), then find the 3 cells on the same row between O and BG that have the corresponding boxed the 2 digit pairs. (O=01, P=02, Q=03, R=04, S=05, T=06, U=07, V=08, W=09, X=12, Y=13, Z=14, AA=15, AB=16, AC=17, AD=18, AE=19, AF=23, AG=24, AH=25, AI=26, AJ=27, AK=28, AL=29, AM=34, AN=35, AO=36, AP=37, AQ=38, AR=39...

MLK b-day as holiday date
hi, i need a function to calculate what date would be the 3rd monday in January for a given year. can this be done w/o arrays? thanks -- ______ Regards, Greg Try this... =DATE(A1,1,22)-WEEKDAY(DATE(A1,1,6)) The general formula is: =DATE(year,month,1+N*7)-WEEKDAY(DATE(year,month,8-day_of_week)) Where: N = the nth weekday (3 for the 3rd Monday) day_of_week = a number from 1 to 7. 1 = Sunday thru 7 = Saturday -- Biff Microsoft Excel MVP "Greg" <Greg@discussions.microsoft.com> wrote in message news:95E9B150-2CA6-4143-8453-63FA6DA260...

Time-stamping a chart
I am querying a database and charting the info which I want to publish on a web page. I'd like to time and date of the latest query result to appear on the chart so that users know how current the information is. Maybe even embed the last update info in the chart title? That would work... Something like... "Average Time - Last updated 01/08/2004 9:16 AM EST" Any ideas? How is the query executed? Is it through custom VBA code? Or through native XL capabilities? If the former, use the code to put the desired title in a cell. Link the cell to the chart title. For ...

Rounding up Time Values
Hi I am using the formula below to round time values up,in this case t the next five minute increment =(TIME(HOUR(BB14),CEILING(MINUTE(BB14),BB13),0)) BB13 = 5 BB14 = 16:00:02 This formula only rounds up to 16:05:02 if the time is 16:01:00 o greater,i would prefer it if the formula could make the time round u as soon as one second has passed, 16:00:01 and not when one minute ha passed. Can this be done Ad -- Message posted from http://www.ExcelForum.com What is in BB14? You can just use =CEILING(BB14,5/1440) will do what you want Or if there are dates as well you can just format...

Query based Subform will not allow editing
I have a maintenance DB that has energy lockout points associated with pieces of equipment so we can safely do maintenance. I have an 'edit existing lockout' form, based on a query, that lists in a subform the various points required for a certain piece of equipment. My system used to work, but now I can edit the main, but the subform has locked me out. All I get is a doorbell tone when I attempt to enter info. I can enter the req'd info in the tables, but but my form went snafu. Any ideas? Thanks in advance Does your query allow editing? Check the asterisk in the record...

Merging Workbook Table data Based upon Value comparisons
I have two workbook tables (Two different workbooks) with two matching column names. What I wish to do is to merge values from one table to another, but ONLY for those records inwhich these two columns have matching values. Would this be possible? Jay Are you saying you have two workbooks, or are the tables within one workbook? "jayceejay" <jayceejay@discussions.microsoft.com> wrote in message news:AC73B2C7-83EF-4D27-A464-32AEEE7D4214@microsoft.com... >I have two workbook tables (Two different workbooks) with two matching >column > names. What I wish to do ...

How to get one field to update based on a selection from a drop do
New to Access. I want to create a form that coworkers can use to lookup information in a database for inputting data into a PO as well as submit new records into the database. I want to have a control that is a combo drop down box where the can select a row from a list of items in a specified field and based on that selection it will update the below text box control with the corresponding data in the field next to it on the database. Make sense? Basically need to figure out how to update one control based on data selected from the drop down box control above it??? Private Sub...

changeing the time CRM sends emails
Hello I have set up various workflow rules that send an email reminder for us a couple of weeks before a contract expires. Is there a way to set the time that these email reminders are sent? At the moment they are coming through at 00.00 which is a pain if you have a blackberry by the bed and they do not get actioned staright away. Any advice would be greatly appreciated Thanks ...

how do I record times from track meets and find averages
I need toot been able keep track of athletes time and find averages. I have not been able to figure it out. go over to debra's site: http://www.contextures.com/excelfiles.html some files have been provided for download. check what fits you requirement -- Message posted from http://www.ExcelForum.com ...

Run-time error'3078'
Hi, I had an issue with data not syncing,because it kept referencing the old spreadsheet, so I went into tools, database utilities, linked manager to update the linked file, now I'm getting: The record source 'Select tbl_JobTracking.[job tracking id], tbl_JobTracking.WMS,'tbl_JobTracking.Discipline, tbl_jJobTracking.SvcCtr, tbl_JobTra...' specified on this form or report does not exist. You misspelled the name, or it was deleted or renamed in the current database, or it exist in a different database. In the Form or Report's Design view, display the property sheet ...

how do I chage mm/day format display to "workweek" display?
I'd like to change the default format in which the "weeks" are displayed, which is starting date of the week. Instead or in addition to that I'd like to display it as a work week. for e.g. the Week of july 3th would be 26ww while the week of july10th would be 27ww. ...

time to decimal
I have numbers like 283 hours 13 min 7 sec 283:13:07 in an excel work sheet. I want to convert this to decimal minutes. What formula can accomplish this? I hope someone can help because I have lots of them to do. Thanks for any help, =A1*24*60 Format as General or number, not as Time, which is what Excel does automatically -- Kind regards, Niek Otten Microsoft MVP - Excel "Louie" <me@my.com> wrote in message news:ePZ%23UeIIIHA.4296@TK2MSFTNGP04.phx.gbl... |I have numbers like 283 hours 13 min 7 sec 283:13:07 in an excel work | sheet. | | I want to conve...

Microsoft CRM 3.0 ignoring british summer time
Hello, We've got a installation of CRM 3.0 with rollup update 2 installed. Win 2k3 EE SP1 as guest on ESX 2.5 host. The guest servers are on BST (GMT+1), however any dates get saved on GMT on the database. This throws reporting off which is a serious concern around here. MS seemed to have pulled rollup update 3, however I went through the list of individual updates and none seem relevant Any ideas? Anybody suffering from the same thing? TIA The Crm database allways stores time in GMT. The conversion to and from local time is done in the user interface. The CRM database does...

Microsoft Knowledge Base Article
I am getting an error with Outlook - "Some items could not be deleted. They were either moved or already deleted, or access was denied." I found the error in the MS Knowledge base with one difference. The article refers to the error occuring after deleted 5000+ items, however this error is happening when the user tries to delete 10 or so e-mails. He does not get the error everytime he deletes items. Any ideas anyone? Are you using exchange server and does it have AV running on it? Can you delete them if you use shift+delete? if so, known issue. -- Diane Poremsky [MVP - O...

Total a column from sheet 2 based on value in sheet 1
Col B Col C Col F Mary Team 1 $331.00 George Team 1 $222.00 Sam Team 2 $186.00 Tom Team 2 $100.00 Above is an example of my data on Sheet 2. On Sheet 1, I want to total all the total funds raised per Team shown on Sheet 2. I am trying to create a summary of what each team raised. I tried using the formula: =SUM(('sheet 2'!F2:F482=Sheet1!B2)) and I get just a dash in my total col. Can anyone provide some help? Thanks Look in HELP for the SUMIF() function -- Kind regards, Niek Otten Microsoft MVP - Excel "Nee...

Serial #'s and workorders
I sell alot of computer parts that are serialized and what ticks me off is that if I scan in parts & serial #'s and create a workorder it'll save the serial #'s, but if I make any change at all to the workorder all the serial #'s go bye bye! Also, when ringing up the the sale and finalizing the workorder it asks for serial #'s and if the customer decides to add another item to the sale I need to cancel completing the sale and go back to the work order screen and add in the product, but again this will loose all the serial #'s. Anyways, I'm sick of havin...

Run-time error after screen lock in XP
In several Excel files I noticed the following error. VBA code works fine until screen is locked by screensaver or pressing Ctrl-Alt-Del and choosing Lock Computer. After unlocking VBA code that worked fine before, now brings the run-time error like run-time error 380. We have OfficeXp SP3 installed. Any solution? ...

All day event (Birthday) start time setting for a WORK day, not full day.
Hello, this problem is literally robbing my sleep. I was using Outlook 2003 and now updated to Vista and Outlook 2007. If I create a new contact and enter his/her birthday, Outlook will create a new all day event for this birthday. This is being synched to my Windows Mobile PDA. So far so good. In OL 2003 this birthday "all day event" would remind me 15 minutes before I start working at 8 am. After the Upgrade 2007 now starts an all day event at 0:00 am. This means, my PDA now reminds me at 11:45 in the night about a birthday. How can I set this back? Or how can I tell Outloo...

Question on delete mail > 30 days? Please help.
I have created 5 mail boxes for store a large volume email (for management), In existing, the mails in the mail boxes are delete every week manually since they use up more than 3GB HD. How to set a rule/resouce kit/any SDK to delete all the mail date longer than 30 in these five mailboxes? I would like to schedule it to delete mail date longer than 30 days every mid-night. Thank you! You could try creating a mailbox managee settings recipient policy set to delete items older than X days from user mailboxes with a custom attribute value of a specified string. Thank You so much, I just go...

Conditional Formatting Across Multiple Cells
I have data file that I want to conditional format Red/Green fill based on >= conditional on a single row of data. In Excel 2003 it was fairly simple to lock a row but not a column and by selecting all the cells to format it would change the column relative to the cell. Just having an issue transitioning to conditional formatting in 2007. Sample Data a b c d 1 goal 10 245 125 2 day1 8 200 76 3 day2 8 250 125 4 day3 15 250 130 5 day4 15 300 150 6 day5 0 100 0 7 Avg 9 220 96 Cells Rows 2 - 7 should be conditional on Row ...

Calendar: print Daily style, 2 pages/day, no TaskPad, no Notes
Hello -- The Notes component of the Daily format still prints, even after I uncheck the Notes area box. The setting for Layout: 2 pages/day does not work. I tried setting up a printer to print 2 pages / landscape page and that did not work either. Can anyone suggest what I can do to have 2 consecutive days print side-by-side on an 8.5" x 11" landscape page? Thanks for any help. Larry Mehl ...

outlook times out sending e-mail
When sending e-mail, my outlook says it is send 5 messages when I have none in my outbox. Any new messages sit in the out box and will no send. I have the newest Norton Antivirus. A virus scanrequest pops up during the send cycle, bu is too quick and I can't see the message. any ideas?? R. MELAND <anonymous@discussions.microsoft.com> wrote: > When sending e-mail, my outlook says it is send 5 messages > when I have none in my outbox. Any new messages sit in the > out box and will no send. I have the newest Norton > Antivirus. A virus scanrequest pops up during the s...

block mail based on subject
My wife gave information to some site that I'm not aware of.All the junk mail begins with her name, is there some way to block it or send it directly to the deleted file ?? Please help it's driving me crazy. ...