Quote:
Originally Posted by Catalin.B
This formula assumes that you entered time in a cell formatted as text, and in G2 is the refference time, in format 16:00 PM
=SUMPRODUCT((TIME(LEFT(B11:B15;2);RIGHT(B11:B15;2) ;0)<=G2)*1)
Or, if you keep the same format in G2, simply: =SUMPRODUCT((B11:B15<=G2)*1)
To take into account the area, entered in F2: =SUMPRODUCT((TIME(LEFT(B11:B15;2);RIGHT(B11:B15;2) ;0)<=G2)*(A11:A15=F2))
Note: it is not an array formula, confirmed with CSE, so so it is not consuming so much resources. For another approach, using COUNTIF 
=COUNTIF( IF(B11:B15<=G2;A11:A15);F2)
The red formula, passes to countif a range with only the rows that qualifies to the time criteria, so now you have a COUNTIF with 2 criteria... |
I really like the countif approach. It does not quite work though. I changed the semi colons to commas and that got it to function but it is not counting just the cells with the desired before time it just counts the amount of times the area appears. I attached a sample so you can see the result. Again the numbers are all formatted as a number so a simplified sumproduct formula I guess could work but I really do like the countif formula.
Thanks so much for your help so far...
03-24-2012, 11:44 AM
Similar Threads
help with formula?
Windows 7 64bit 
Office 2003
Threaded Mode