# match in range

#### Peter1973

##### Well-known Member
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.

### Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
=sumproduct(--(right(e1:e100,3)=a1),--(left(e1:e100,5)+0 > =b1),--(left(e1:e100,5)+0 < =c1))

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

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

Replies
6
Views
212
Replies
3
Views
228
Replies
3
Views
135
Replies
5
Views
232
Replies
11
Views
278

1,203,742
Messages
6,057,112
Members
444,905
Latest member
Iamtryingman

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back