match in range

[Peter1973]

I am trying to find a way to simplifu a lot of work with one formula, any help appreciated.

I have in cell A1 a day ie Sun
in cell B1 a time ie 16:00
in cell C1 a time ie 16:15

I need to look in the range E1:E100 they will contain values like 16:12 Sun, 18:00 Mon etc, I need to look for any cells that the last 3 characters match A1 then in those cells look at the first 5 characters ( will be a time ) and count how many of them are between the times in B1 and C1.

Hope this is clear any further info required please let me know.

 

[c_m]

=sumproduct(--(right(e1:e100,3)=a1),--(left(e1:e100,5)+0 > =b1),--(left(e1:e100,5)+0 < =c1))

 

[Peter1973]

Thanks have tried this but returning #VALUE! any ideas where i may be going wrong ?

 

[barry houdini]

c_m's formula should work.....but only if all cells in E1:E100 contain data in the format you describe, i.e. hh:mm ddd. If there are any blanks or cells not in that format you may get #VALUE! error.

Try restricting the range to the exact data range......or use a dynamic range......or try this "array formula" instead

=SUM(IF(RIGHT(E1:E100,3)=A1,IF(ISNUMBER(LEFT(E1:E100,5)+0),IF(LEFT(E1:E100,5)+0>=B1,IF(LEFT(E1:E100,5)+0<=C1,1)))))

confirmed with CTRL+SHIFT+ENTER