How to Use COUNTA Function with Criteria in Excel (4 Methods)

This article will demonstrate four ways to use the COUNTA function with criteria to count a dataset, no matter whether the values are text, numbers, errors or any other format.

The COUNTA Function in Excel

The COUNTA function in Excel counts all the non-empty cells including numbers, text, errors, empty text (“”), formulas returning null values, etc.

Generic Formula:

Where,

How to Utilize COUNTA Function with Criteria in Excel: 4 Ways

We will implement the COUNTA function with criteria to calculate cells not equal to a range, to calculate the number of non-blank cells not equal to zero (0) and with a formula returning a null value, to automatically sum values after adding them to a table array, and to produce segmentation statistics.

Criteria 1 – Counting Cells Not Equal to a Range

Suppose we want to count cells that are not equal to a range of certain values.

For instance, in the dataset above we have the name of Cricket Premier leagues from which we want to count how many cells have similar kinds of names.

We exclude Big Bash, Mzansi, and County and place these values in another table, then apply the following formula:

This returns the count of how many matching cells are in the original column.

Formula Breakdown:

• COUNTIF(B5:B12,D7:D9) -> holds the values that we don’t want to count (D7:D9) and generates a count in a range of cells (B5:B12).

Output: 1, 1, 1

• SUMPRODUCT(COUNTIF(B5:B12,D7:D9)) -> sums all the items obtained from the above COUNTIF formula.

Output: 3

• COUNTA(B5:B12)-SUMPRODUCT(COUNTIF(B5:B12,D7:D9)) -> based on the output from the SUMPRODUCT function, it resolves to COUNTA(B5:B12)-3, subtracting the sum of the count of the things that we don’t to count from the original total to return the final result.

Output: 5

Without Big Bash, Mzansi, and County in the original dataset, the final data count is 5.

Read More: Dynamic Ranges with OFFSET and COUNTA Functions in Excel

Criteria 2 – Counting Non-Blank Cells not containing Zero (0) and with a Formula Returning Null Values

Suppose we have a dataset with a lot of important information, but also containing some unnecessary values such as zero (0) and a formula that returns a null value. We want to know how many cells hold important information without all the irrelevant values.

For instance, in the dataset below, we want to know how many non-blank cells are present without a zero or formula returning a null value. 

In cell B6, we have a formula that returns a null value (see the picture below).

We implement the following formula to extract the result:

We have 2 cells in the data table that are without a zero and null value returning formula.

Formula Breakdown:

• COUNTIF(B6:B9,”=0″) -> counts how many zeros are in the selected data range (B6:B9).

Output: 1

• COUNTBLANK(B6:B9) -> counts how many blank cells are in the selected data range (B6:B9).

Output: 1

• COUNTA(B6:B9) -> counts how many non-empty cells are in the data range.

Output: 4

• The final formula, COUNTA(B6:B9)-COUNTBLANK(B6:B9)-COUNTIF(B6:B9,”=0″) -> resolves to 4-1-1

Output: 2

So we have 2 non-blank cells in our dataset without a zero and null value returning formula.

Criteria 3 – Automatic Sum after Adding Values

Suppose we have a huge dataset of sales values and the sum of these large datasets. If we want to add a new sale value, we’d have to update the whole dataset. But using the COUNTA function in combination with some other functions, we can continuing adding new sales values and the total value will automatically keep updating.

The formula:

The above formula is based on the below dataset that shows the detailed calculation of how to achieve automatic sum after each added value.

Formula Breakdown:

• COUNTA(C:C) -> counts the total number of cells in the whole column C.

Output: 6, which is the total number of cells in C4:C9.

• COUNTA(C:C)-1) -> subtracts 1 to remove the header cell.

Output: 5

• OFFSET(C4,1,,COUNTA(C:C)-1) -> resolves to OFFSET(C4,1,,5) which returns the reference to the cell 1 row below and 0 columns to the right of C4, and a height of 5 which refers to cells C5:C9.

• SUM(OFFSET(C4,1,,COUNTA(C:C)-1)) -> becomes SUM(C5:C9) and adds each number in C5:C9 to get the final result.

When adding a new values in cells at the bottom of the dataset, the result value in cell F7 automatically updates.

When a new number is added in cell C10, the last row of column C, the counted result of COUNTA(C:C) becomes 7, 7 minus 1 becomes 6, then OFFSET returns C4:C9, so every time a row is added to the end the table, the formula will automatically add it.

Read More: [Fixed] Excel COUNTA Function Not Working

Criteria 4 – Producing Segmentation Statistics

We can count the number of sales in January and February months using the COUNTA function combined with some other functions.

The formula is:

 And the dataset is:

Formula Breakdown:

• (ROW()-5)/4)*4 -> here 4 means that one segment in the table array is four rows. ROW() returns the row number (5) of the cell where the formula is located, which is 0.

• INT((ROW()-5)/4)*4 -> the INT function is used for rounding, and returns 0.

• OFFSET($D$5:$D$8,INT((ROW()-5)/4)*4, ) -> resolves to OFFSET(($D$5:$D$8, 0*4), which returns the reference to the cell 0 rows below and 0 columns to the right of D5 and the same height and width as D5:D8, returned as a table array.

• COUNTA(OFFSET($D$5:$D$8,INT((ROW()-5)/4)*4, )) -> becomes COUNTA(($D$5:$D$8).

Output: 4

• IF(MOD(ROW()-5,4)=0, COUNTA(OFFSET($D$5:$D$8,INT((ROW()-5)/4)*4, )),””) -> becomes IF(MOD(ROW()-5,4)=0, 4, “”). Since ROW() returns 5, MOD(ROW()-5,4) becomes MOD(5-5, 4), then the Mod function resolves to modulo 0 and 4, with the result being 0. So the formula becomes =IF(0=0,4,””). Since the condition 0=0 of the IF function is established, it returns 4.

Once you are done with the first segment (January month), simply drag the row down using Fill Handle to apply the formula in the rest of the cells o count how many sales were in each month.

Download Practice Template

Related Articles

• How to Use COUNTA from SUBTOTAL Function in Excel
• Difference Between COUNT and COUNTA Functions in Excel

<< Go Back to Excel COUNTA Function | Excel Functions | Learn Excel