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.
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.
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.
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.
The formula looking at the number of incidents in Lee would read as
You could also type it as =COUNTIF($B:$B,”Lee”)
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
would return 2 because in the full list of data, two alcohol incidents occurred in the Lee building.