Conditional Sumproduct() Based on the Time of the Day

[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

 

[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

 

[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

 

[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