Countif & Array formula

  • Thread starter Thread starter Wheelman
  • Start date Start date
W

Wheelman

I am attempting to count by array formula the number of times
particular entry ("Unavailable") in a column occurs which falls withi
a particular time range from time entries in a previous column. We ar
developing a spreadsheet to track reqests for service and the time the
occur or are denied.
I'm using the "Countif" function to identify the occurances of th
"Unavailable" entry but then need to only select those within certai
time ranges (e.g., >=06:00). I've been unsuccessful with a number o
attempts with array formulas such as the following:
{=COUNTIF(F2:F30,"=Unavailable"+COUNTIF(B2:B30,">=06:00"))}
The first Countif statement will correctly count the "Unavailable
occurences, but after the 2nd countif statement is nested it will no
select the correct number of occurences and returns "0".
I'm using Excel 2000 for the application.
Thank-you
 
An array formula isn't required for this,
With 06:00 (or whatever time) in A1
=SUMPRODUCT((F2:F30="Unavailable")*(B2:B30>=A1))
Regards,
 
Hi
try something like
=SUMPRODUCT(--(F2:F30="Unavailable"),--(B2:B30>=TIME(6,0,0)))

or if you also have a upper boundary use:
=SUMPRODUCT(--(F2:F30="Unavailable"),--(B2:B30>=TIME(6,0,0)),--(B2:B30<
TIME(12,0,0)))
 
Back
Top