Which of the following functions is best suited to count numbers based on multiple conditions?

Summary 

The Excel COUNT function returns a count of values that are numbers. Numbers include negative numbers, percentages, dates, times, fractions, and formulas that return numbers. Empty cells and text values are ignored.

Syntax 

=COUNT [value1, [value2], ...]

Arguments 

• value1 - An item, cell reference, or range.
• value2 - [optional] An item, cell reference, or range.

Usage notes 

The COUNT function returns the count of numeric values in the list of supplied arguments. COUNT takes multiple arguments in the form value1, value2, value3, etc. Arguments can be individual hardcoded values, cell references, or ranges up to a total of 255 arguments. All numbers are counted, including negative numbers, percentages, dates, times, fractions, and formulas that return numbers. Empty cells and text values are ignored.

Examples

The COUNT function counts numeric values and ignores text values:

=COUNT[1,2,3] // returns 3 =COUNT[1,"a","b"] // returns 1 =COUNT["apple",100,125,150,"orange"] // returns 3

Typically, the COUNT function is used on a range. For example, to count numeric values in the range A1:A10:

=COUNT[A1:A100] // count numbers in A1:A10

In the example shown, COUNT is set up to count numbers in the range B5:B15:

=COUNT[B5:B15] // returns 6

COUNT returns 6, since there are 6 numeric values in the range B5:B15. Text values and blank cells are ignored. Note that dates and times are numbers, and therefore included in the count.

The COUNTA function works like the COUNT function, but COUNTA includes numbers and text in the count.

Functions for counting

• To count numbers only, use the COUNT function.
• To count numbers and text, use the COUNTA function.
• To count with one condition, use the COUNTIF function
• To count with multiple conditions, use the COUNTIFS function.
• To count empty cells, use the COUNTBLANK function.

Notes

• COUNT can handle up to 255 arguments.
• COUNT ignores the logical values TRUE and FALSE.
• COUNT ignores text values and empty cells.

COUNT formula examples

Data validation rules are triggered when a user adds or changes a cell value. When a custom formula returns TRUE, validation passes and the input is accepted. When a formula returns FALSE, validation fails and the...

Working from the inside out, the MID function is used to generate an array from text entered in B5 with this snippet: MID[B5,ROW[INDIRECT["1:"&LEN[B5]]],1] explained in detail here. The result is an...

In this example, the goal is to count the unique dates in a range of timestamps [i.e. dates that contain dates and times]. In addition, we also want to create the table of results seen in E7:F9. For convenience, data is...

Traditionally, counting unique items with an Excel formula has been a tricky problem, because there hasn't been a dedicated unique function. However, that changed when dynamic arrays were added to Excel 365, along with...

The goal of this example is to verify input before calculating a result. The key point to understand is that any valid formula can be substituted. The SUM function is used only as an example. The logic can also be...

In this example, the goal is to count the number of exact matches in two ranges, ignoring the sort order or location of the values in each range. This problem can be solved with the COUNTIF function or with the MATCH...

The OFFSET function can be used to construct dynamic ranges using a starting cell, and given rows, columns, height, and width. The rows and columns arguments function like "offsets" from the starting reference. The...

The OFFSET function can be used to construct dynamic rectangular ranges based on a starting reference and given rows, columns, height, and width. The rows and columns arguments function like "offsets" from the starting...

In this example, the goal is to count the number of cells in a range that contain numbers. This problem can be solved with the COUNT function or the SUMPRODUCT function. Both methods are explained below. COUNT function...

The FIND function returns the position of the text when it's found in the cell, and the #VALUE! error if not. In the example, the cell B4 contains the number "2" in the 29th position. So, if we just used this formula...

This formula first generates a rank value using an expression based on COUNTIF: =COUNTIF[data,"