Excel: COUNT, COUNTIF, COUNTIFS

Office 365 / SharePoint Blog

Many times in Excel you will want to get an idea of how many records you might have, or you want to count certain things within your records. To do that you can use COUNT, COUNTIF, COUNTIFS, COUNTA, and COUNTBLANK.

I will cover COUNTA and COUNTBLANK in another post.

COUNT

The COUNT formula counts numbers and ignores empty cells, text fields, TRUE/FALSE logical values. It only counts numerical values.

=COUNT(1,3,”VT”,5,8,”Hokie”) would return 4, as there were 4 numbers within those values.

=COUNT(B1:B18) in the T-shirt example below would return 0 as there are no numbers in the B column.

=COUNT(C1:C18) in the T-shirt example would return 15 as there are 15 numbers in the column.

Excel: COUNTA example

COUNTIF and COUNTIFS

The COUNTIF formula gives you a number count for any criteria you set, text or numerical.

For example, Housing and Residence Life might collect data showing incident reports for their residence halls.

Excel: COUNTIF formula example

Using COUNTIF, we can take all the incident reports from all the buildings, and then pull out information by building, by day of the week, or by type of incident.

The way it works, is you first choose the range or values you want the formula to examine, and then select the criteria.

=COUNTIF(range, criteria)

The formula looking at the number of incidents in Lee would read as

=COUNTIF($B:$B,E3)

You could also type it as =COUNTIF($B:$B,”Lee”)

COUNTIFS

The COUNTIFS formula lets you set multiple criteria on any cell content, numerical or text.

The way it works, is you first choose the range or values you want the formula to examine, and then select the criteria, then the next range, then the next criteria and so on.

=COUNTIF(range, criteria, range, criteria)

So using the above example, a formula that reads

=COUNTIFS(B:B,”Lee”,C:C,”Alcohol”)

would return 2 because in the full list of data, two alcohol incidents occurred in the Lee building.

Advertisements

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