Forum Discussion
Help with a formula (countif+contains?)
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.
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 Replies
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.
- Perfect. Can't thank you enough!
You're welcome! Glad to have been able to help.
