Count rows in excel with condition

There are five common functions that you can use to count rows or columns that contain data. The functions are COUNT(), COUNTA(), COUNTIF(), COUNTIFS(), COUNTBLANK(). At this point, you might be wondering why five functions should there even be since what we really wanted to do is to just count the rows and columns that contain values. Well, let me take you a quick walkthrough for each of the five functions.

The COUNT() function returns a count of values that are numbers in rows or columns. For example, if you have a worksheet that shows the participants for an event, one column shows that participant name and the other column shows the participant age. You can use the COUNT() to quickly count the number of participants for the event.

Function are written as:

=COUNT(value 1,[value 2]...)

Value 1 can be an item, cell reference or range, Value 2 is optional, it can also be an item, cell reference or range.

The COUNTA() function count everything except empty cells. It count cell that contain values, numbers, text, logical operators, errors and empty text(" "). For ease of remembering, you can take the "A" in the part of this function as "All".

Function are written as:

=COUNTA(value 1,[value 2]...)

Value 1 can be an item, cell reference or range, Value 2 is optional, it can also be an item, cell reference or range.

The COUNTIF() function count cell in a range based on a given criteria. For example, if you are a teacher and you have a spreadsheet that shows all the students in the class with one column showing their name and the other showing their student gender, you can easily use the COUNTIF() function, specifying a gender parameter to quickly count how many of the student are male and how many of the student are female.

Function are written as:

=COUNTIF(range, criteria)

Range refer to the range of cell to count, the criteria refer to the condition you specify.

The COUNTIFS() is similar to the COUNTIF(), the difference is the former function count cell in a range based on more than a single criteria. For example, referencing on the example on students in the class above, you can specify a condition on gender and also another condition on the student nationalities.

Function are written as:

=COUNTIFS(range 1, criteria 2, [range 2], [criteria] 2...)

Range 1 refer to the first range to evaluate, Criteria 1 refer to the condition to specify on Range 1. Range 2 refer to the second range to evaluate, Criteria 2 refer to the condition to specify on Range 2. You can specify multiple range and criteria in this function. 

Lastly, we have the COUNTBLANK() function. This function counts all the empty cell in a range. Formulas that return empty cells are counted too.

Function are written as:

=COUNTIF(range)

The range in which to count blank cells.

Check out the video at the start of this article to learn in detail on the five counting function of excel.