How to use cell reference to match character string

[shanteshmnnit]

A B C D E
1 IC609 Mumbai D Mumbai 3
2 SG208 Bangalore D
3 9W328 Mumbai 1,3,4,5,6,7
4 9W654 Mumbai 1,2,3,4,5,6
5 S2456 Mumbai D
6 DN716 Delhi D
7 IC614 Delhi D
8 IC7267 Hyderabad 1,5


The data shows the flights from Ahmedabad to various destinations. They are operative on the the weekdays mentioned in Column C. D= Daily, 1=Monday, 7=Sunday. I have to find the no. of flights to particular destination on a particular day. Eg. No. of flights to Mumbai on Tuesday=2.
I tried using the COUNTIFS function to do that. The formula that I used can take care of a particular day of the week (1,2, or so on) but in general if the day of the week is to be taken as a cell reference, I am not able to do that.
My formula =COUNTIFS(B1:B8,D1,C1:C8,"*3*")+COUNTIFS(B1:B8,D1,C1:C8,"D"). As it is evident that the formula only works for day 3, it can not use the cell reference of E1 to find the no. of flights on a any particular day given in E1. How to do that?

[samba_ravi]

attach a sample file with desired result

[shanteshmnnit]

I have attached the problem here.

[samba_ravi]

=SUM(INDEX(($B$3:$B$17=$E7)*(($C$3:$C$17="D")+ISNUMBER(SEARCH(3,$C$3:$C$17))),0))
Try this

[shanteshmnnit]

Thanks.. it worked just had to change one thing i.e. using the cell reference F7 in place of "3" in ISNUMBER(SEARCH(3,$C$3:$C$17))) to incorporate any day in that cell. Anyways, can u please explain the formula as I don't have much idea about the functions that you used.

[FlameRetired]

One way.....using F7 for the day (as in the upload) is this

Please Login or Register  to view this content.

To use E1 use this

Please Login or Register  to view this content.

Put the day number in E1 (or F7 depending).

[shanteshmnnit]

Thanks.. This one I can understand. I was having problem in using the cell reference to check the day from the array. I was not familiar with the syntax. Thanks a lott.. !!