Excel: CountIF Function

COUNTIF:

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.  

1. Open the file in Excel, and  delete the contents of D18 .
2. On the Formulas Tab, click the Insert Function button. The Insert Function dialog appears.
3. Locate the COUNTIF function.
   tip: The Search for a function box can help you quickly locate new or unfamiliar functions
4. Click OK. The Function Arguments dialog box appears.
5. Click in the RANGE field, then Select cells or type the reference D3:D15
6. 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.
7. 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.

Cheers!