Google Sheets - Counting Items
Many people that use sheets know how to count the number of cells with numerical entries using the =COUNT function. For example, here's a spreadsheet where I've documented my running minutes and I want to know how many days I ran:
Pretty simple. But what if I want to know how many cells within a range have alphanumeric entries? Or what if I want to know how many cells within a range meet certain criteria?
There are actually a whole series of count functions available in Google Sheets:
COUNTA: will count the number of cells with any entry (nonblank) in a range. For example, I've added in my strength & conditioning workouts on those days. Using =COUNTA(range) I can document the total number of days I worked out whether it was running or another workout.
COUNTIF: counts the number of cells within a range that meet a certain criteria. This formula has two pieces - the range to check, followed by the criteria to match. If the criteria is text (like in my example) it needs to be within quotation marks. In my example, I used the =COUNTIF formula to see how many days I did an abs workout:
COUNTIFS: counts the number of cells within a range that meet multiple criteria. With my simple example this would be impossible to demo, so here is just a data set where I have asked it to count the number of rows when both the first column is greater than 15 and the second column is less than 15:
COUNTBLANK: counts the number of blank cells in a range. For example, I can use it to see how many days I was lazy and did no workout:
COUNTUNIQUE: counts the number of unique entries in the range. In my example, I can see how many different workouts I did. This is somewhat of a bogus example as it will count all runs of different lengths as different workouts...but it will give you the picture!
Pretty simple. But what if I want to know how many cells within a range have alphanumeric entries? Or what if I want to know how many cells within a range meet certain criteria?
There are actually a whole series of count functions available in Google Sheets:
COUNTA: will count the number of cells with any entry (nonblank) in a range. For example, I've added in my strength & conditioning workouts on those days. Using =COUNTA(range) I can document the total number of days I worked out whether it was running or another workout.
COUNTIF: counts the number of cells within a range that meet a certain criteria. This formula has two pieces - the range to check, followed by the criteria to match. If the criteria is text (like in my example) it needs to be within quotation marks. In my example, I used the =COUNTIF formula to see how many days I did an abs workout:
COUNTIFS: counts the number of cells within a range that meet multiple criteria. With my simple example this would be impossible to demo, so here is just a data set where I have asked it to count the number of rows when both the first column is greater than 15 and the second column is less than 15:
COUNTBLANK: counts the number of blank cells in a range. For example, I can use it to see how many days I was lazy and did no workout:
COUNTUNIQUE: counts the number of unique entries in the range. In my example, I can see how many different workouts I did. This is somewhat of a bogus example as it will count all runs of different lengths as different workouts...but it will give you the picture!
