Help with a formula (countif+contains?)

[reevesgetsaround]

Spreadsheet warriors, this should be an easy one. I'm very out of practice and keep tripping myself up.

All I need is a formula in D5 to return a count of ticks in a given column from a range of titled columns, based the selection in the dropdown list of those same titles. e.g. Option 9 selected would return 1, Option 11 would return 3.

I tried a COUNTIF with a CONTAINS but I don't think that's right.

 

 

Any assistance would be greatly welcomed.

[#2 · Accepted solution]

@reevesgetsaround 

COUNTIF(INDEX('Other Sheet'!$F$7:$S$37,0,MATCH(B5,'Other Sheet'!$F$6:$S$6,0)),"*")

or

=SUMPRODUCT(('Other Sheet'!$F$7:$S$37<>"")*('Other Sheet'!$F$6:$S$6=D5))

where Other Sheet is the name of the sheet with the data.

[#3]

Perfect. Can't thank you enough!

[#4]

@reevesgetsaround 

You're welcome! Glad to have been able to help.