Quickly Change Results for Excel COUNTIF Function

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.

countifop02

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.

CountIfOpList01

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.

CountIfOpList02

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.

CountIfOpList03

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.

____________

You may also like...

2 Responses

  1. Jeff Weir says:

    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

  2. Thanks Jeff, glad you like the idea.

Leave a Reply

Your email address will not be published. Required fields are marked *