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:**

`=COUNTA(value1, [value2],…)`

Where,

Arguments | Description |
---|---|

value1 |
a cell reference or range |

value2 |
a cell reference or range |

**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:

`=COUNTA(B5:B12)-SUMPRODUCT(COUNTIF(B5:B12,D7:D9))`

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 Value****s**

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:

`=COUNTA(B6:B9)-COUNTBLANK(B6:B9)-COUNTIF(B6:B9,"=0")`

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:**

`=SUM(OFFSET(C4,1,,COUNTA(C:C)-1))`

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:

`=IF(MOD(ROW()-5,4)=0, COUNTA(OFFSET($D$5:$D$8,INT((ROW()-5)/4)*4, )),"")`

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**