How to Use COUNTA with Criteria in Excel
Using COUNTA with Criteria in Excel
COUNTA function in Excel is used to count the number of non-empty cells in a range. However, COUNTA itself does not support criteria. To count cells based on certain criteria, you can use COUNTIF, COUNTIFS, or a combination of SUMPRODUCT and ISNUMBER functions.
Here's how to use each of these functions to count cells based on criteria:
- COUNTIF
COUNTIF function is used when you have only one criterion.
Syntax:
=COUNTIF(range, criteria)
Example: Let's say you have a list of fruits in column A (A1:A10) and you want to count the number of cells that contain the word "Apple".
=COUNTIF(A1:A10, "Apple")
- COUNTIFS
COUNTIFS function is used when you have multiple criteria.
Syntax:
=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]...)
Example: Let's say you have a list of fruits in column A (A1:A10) and their quantities in column B (B1:B10). You want to count the number of cells that contain the word "Apple" and have a quantity greater than 5.
=COUNTIFS(A1:A10, "Apple", B1:B10, ">5")
- SUMPRODUCT and ISNUMBER
If you want to count cells based on criteria that involve text manipulation or more complex conditions, you can use SUMPRODUCT and ISNUMBER functions.
Syntax:
=SUMPRODUCT(--(ISNUMBER(SEARCH(substring, range)))))
Example: Let's say you have a list of fruits in column A (A1:A10) and you want to count the number of cells that contain the word "Apple" as part of their text.
=SUMPRODUCT(--(ISNUMBER(SEARCH("Apple", A1:A10))))
In this example, the SEARCH function looks for the word "Apple" within each cell in the range A1:A10. The ISNUMBER function returns TRUE if the SEARCH function finds the word "Apple", and FALSE otherwise. The double hyphen (--) is used to convert TRUE/FALSE values into 1/0. Finally, the SUMPRODUCT function adds up these values to give the final count.