Thread: [Solved] formula help
View Single Post
 
Old 03-24-2012, 05:49 AM
Catalin.B Catalin.B is offline Windows Vista Office 2010 32bit
Expert
 
Join Date: May 2011
Location: Iaşi, Romānia
Posts: 386
Catalin.B is on a distinguished road
Default

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...
Reply With Quote