How to Aggregate the COUNTIF Function in Excel - 4 Methods

The sample dataset showcases Sales Data: Product names, sales Date, and Sales in USD.

excel aggregate countif

Method 1 – Utilizing COUNTIF Function with Greater Than and Less Than Criteria

To find Sales values between $1500 and $4000.

Steps:

Select C19 and enter the following formula.

=COUNTIF(D5:D14,">1500")-COUNTIF(D5:D14,">=4000")

Formula Breakdown:

COUNTIF(D5:D14,”>1500”) → counts the number of cells within a range that meet the given condition. D5:D14 is the range argument that refers to Sales.”>1500” is the criteria argument that returns the count of the matched values.
- Output → 6
COUNTIF(D5:D14,”>=4000”) → D5:D14 is the range argument that refers to Sales. ”>=4000” is the criteria argument that returns the count of the matched values.
- Output → 4
COUNTIF(D5:D14,”>1500″)-COUNTIF(D5:D14,”>=4000″) → becomes
- 6-4 → 2

Utilizing COUNTIF Function with Greater Than and Less Than Criteria

This is the output.

excel aggregate countif for a range

Read More: How to Aggregate Data in Excel

Method 2 – Aggregate 2 Criteria with the COUNTIF Function

To count the occurrences of AC and TV in the dataset:

Steps:

Select C19 and enter the following formula.

=COUNTIF(B5:B14,D16)+COUNTIF(B5:B14,D17)

Formula Breakdown:

COUNTIF(B5:B14,D16) → counts the number of cells within a range that meet the given condition. B5:B14 is the range argument that refers to Sales. D16 is the criteria argument that returns the count of the matched values.
- Output → 2
COUNTIF(B5:B14,D17) → D5:D14 represents the range argument that refers to Sales. D17 is the criteria argument that returns the count of the matched values.
- Output → 2
COUNTIF(B5:B14,D16)+COUNTIF(B5:B14,D17) → becomes
- 2+2 → 4

Aggregate 2 Criteria with COUNTIF Function

This is the output.

excel aggregate countif with 2 criteria

Method 3 – Aggregate 3 Criteria with the COUNTIF Function

Determine the occurrences of AC, TV, and Oven:

Steps:

Select C19 and enter the following formula.

=COUNTIF(B5:B14,D16)+COUNTIF(B5:B14,D17)+COUNTIF(B5:B14,D18)

D16, D17, and D18 are the Criteria AC, TV, and Oven.

Aggregate 3 Criteria with COUNTIF Function

This is the output.

excel aggregate countif with 3 criteria

Method 4 – Using the COUNTIF and the SUMPRODUCT Functions

Find specified dates in the dataset.

Steps:

Select C19 and enter the following formula.

=SUMPRODUCT(COUNTIF(C5:C14,{"01-9-21","02-25-21"}))

Formula Breakdown:

COUNTIF(C5:C14,{“01-9-21″,”02-25-21”}) → C5:C14 is the range argument that refers to Dates. {“01-9-21″,”02-25-21”} is the criteria argument that returns the count of the matched values.
- Output → {2, 2}
SUMPRODUCT(COUNTIF(C5:C14,{“01-9-21″,”02-25-21”})) → becomes
- SUMPRODUCT({2, 2}) → returns the sum of the products of the corresponding ranges or arrays. Here, {2, 2} is the array1 argument which is added to return the number of occurrences of the specified criteria.
- Output → 4

Note: Press CTRL+SHIFT+ENTER instead of ENTER when using Excel versions, other than Microsoft Excel 365.

Aggregate with COUNTIF and SUMPRODUCT Functions

This is the output.

Result with SUMPRODUCT function

Using the COUNTIF Function to Aggregate Counting Values in Excel

To count Sales greater than $1500:

Steps:

Select C19 >> enter the formula below.

=COUNTIF(D5:D14,C17)

Formula Breakdown:

COUNTIF(D5:D14,C17) → counts the number of cells within a range that meet the given condition. D5:D14 represents the range argument that refers to Sales. C17 is the criteria argument that returns the count of the matched values.
- Output → 6

Using COUNTIF Function to Aggregate the Counting Values in Excel