If you ever find yourself trying to perform a count on a select subset of items in a larger range, then you have reason to use the COUNTIF function.
The COUNTIF function allows you to count values in a list provided that those values satisfy a given criteria.
Some examples of it use:
- Count number of donations greater than $100
- Count number of contract employees among a list of all staff
- Count chickens before they have hatched
Okay, that last one is more of a lifehacking skill, and is never a good idea :).
Let’s demonstrate the COUNTIF using the first scenario. Count the number of guests who have donated more than $100
Ignore the other columns, for our purposes, all the action is occurring in column D.The intent is to count the number of cells that have values 100 or greater.
A working (completed) copy of this spreadsheet can be found here, on Google Drive. The file will open in a browser window/tab, in view mode. Click CTRL + S (PC) or select FILE, SAVE AS to download the file.
- Open the file in Excel, and delete the contents of D18 .
- On the Formulas Tab, click the Insert Function button. The Insert Function dialog appears.
- Locate the COUNTIF function.
tip: The Search for a function box can help you quickly locate new or unfamiliar functions
- Click OK. The Function Arguments dialog box appears.
- Click in the RANGE field, then Select cells or type the reference D3:D15
- Click in the CRITERIA field, then type “>=100” (including quotes)*
tip: If you forget the quotes, excel’s intelli-sense will intercede and add them for you. This, however, is not the case when you freehand type the formula.
- Press OK.
Experiment: change some contributions in the spreadsheet and see how the COUNTIF function recalculates. This spreadsheet also has a Conditional Format to highlight those contributions over $100 in gold. A later post will detail how that was created.