Conditional Sumproduct() Based on the Time of the Day

R

Rob

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
 
B

Bob Phillips

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
 
A

Ashish Mathur

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
 
B

Bob Phillips

It will refresh if anything in C3:K7, or any cells in their dependency
cycle, change, just like any other formula.

HTH

Bob
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top