You’re comfortable with Excel, and can change formulas on the fly, when necessary. If you’re creating Excel workbooks for other people to use, their Excel skills probably aren’t as strong as yours.
Last month we looked at the COUNTIF formula, and how you can type an operator, then refer to a worksheet cell, to set a minimum value for counting.
Operator in Referenced Cell
In the comments, Kanti Chiba mentioned that the operator could be included in that referenced cell.
For example, you could type >=50 in a cell, and refer to that cell in the formula.
Create a List of Operators
I usually keep the operator in the formula, so it’s separate from the number, and users won’t have to worry about typing it. Kanti’s comment made me think about other options, and how we could let users select both the operator and the target number.
So, I typed a list of operators on a different sheet in the workbook, and named that list as OpList.
Add a Drop Down List of Operators
The next step was to create a drop down list of operators, in the cell to the left of the Score input cell. I used data validation to create the drop down list, then selected one of the operators.
Change the COUNTIF Formula
The final step was to change the COUNTIF formula, so it refers to the Operator cell. Now the COUNTIF formula results will change, if a different operator is selected.
Watch the Video
To see the steps for creating a COUNTIF formula with a drop down list of operators, please watch this short Excel tutorial video.
More COUNTIF Links
For more examples of using the Excel COUNTIF function, see these blog posts:
Check Winning Numbers with COUNTIF
Use COUNTIFS for Multiple Criteria
Count Cells Greater Than Set Amount
____________
Re “If you’re creating Excel workbooks for other people to use, their Excel skills probably aren’t as strong as yours.” That’s a very very good point. Thanks for the great idea of “pick and mix” criteria
Thanks Jeff, glad you like the idea.