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

CountIF..click to zoom in
CountIF example with D18 highlighted

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 hereon 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!

Advertisements

Author: quincy harley jr

Quincy is a technophile, coach and Learning Development expert. He has an extensive legal IT background and is practiced in MS Office application support and product development. As a project leader he has been integral in numerous new application rollouts. Whatever spare time he has, is spent with reading, archery and watching his young sons grow.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s