COUNTIF function based on cell value from drop-down list

[Pete298]

Hi all,

On my first sheet I have a drop-down list that selects which week of the year we are on (17, 18, 19, etc.).
Then on the second sheet, each week is broken down into 5 days, 1,2,3,4,5. The cell above these 5 is merged with the contents "week 18" for example.

I want to be able to select the week from the drop down list, then excel uses a formula to search the relevant 5 columns and count all the letter "m" within those columns, essentially those that fall under that week header.

Can anybody help please? Not sure best way to approach this. Have tried vlookups, hlookups, indexes and matches, countif, defined name ranges, etc.

Thanks,
Pete

 

[BarryL]

Hi, try the below. Where A2 is the week value from your dropdown list and sheet2!2:11 is the where you want to count anything that has "m" in it.

=IF(COUNTIF(Sheet2!1:1,"*"&A2&"*")>0, COUNTIF(Sheet2!2:11,"*m*"),"")

 

[BarryL]

also possible

=IF(ISNUMBER(SEARCH(A2,Sheet2!1:1)),COUNTIF(Sheet2!2:8,"*m"),"No Values")

 

[Pete298]

Hi Barry,

Neither of these have worked. the first just returns a blank cell and then the second just returns "No Values". It seems like the 'search' for the week number does not locate in the second sheet?

I'm looking for a formula where the 5 days of week 18 are the columns F to J, and the formula searches for the week number and then looks within these specific 5 columns for the letter "m".

Can you provide more assistance please?

Thanks,
Pete