Using COUNTIF With 2 Criteria
You may have already used the countif function, but what if you want to use 2 or more criteria. In that case you can use the CountIfs function, here is how you do so. Countifs is only available from Excel 2007 onwards.
Need Help with COUNTIFS, Formulas and Excel Skills?
We offer live instructor-led Excel training for teams who need practical help with formulas, analysis and day-to-day spreadsheet work.
- Useful for reporting, admin and finance teams
- Live online or onsite delivery
- Support with COUNTIFS, XLOOKUP, summaries and everyday Excel tasks
If you want to you can download the exercise file below so that you can follow along.
- After downloading then click on a cell in which you wish to see the answer. I've clicked on cell E2.
- Click on insert function (fx) on the formula bar at the top.
- If you can't see Countifs in the list then type it in the search box, double click or click ok when you have countifs highlighted.
- Ensure that your cursor is flashing in the top Criteria Range box highlight cells B1:B6.
- Go to the Criteria 1 type ">20"
- Now click in the Criteria Range 2 box and select cells C1:C6
- Go to the Criteria 2 box and type "London".
- Click on OK.
- There you go, have a muck around with the values.
Should you wish to follow along you can use the countif 2 critera exercise file.