Help with a formula (countif+contains?)

Occasional Contributor

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.


3 Replies
best response confirmed by reevesgetsaround (Occasional Contributor)


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




=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.

Perfect. Can't thank you enough!


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