How to Differentiate Between SUMIF and COUNTIF Functions in Excel (2 Examples)

Dataset Overview

We will use the following sample dataset to illustrate the differences:

First, let’s discuss the argument syntax for both functions:

SUMIF Function

The syntax for the SUMIF function is as follows,

=SUMIF (range, criteria, [sum_range])

From the above syntax, you will require the following arguments to perform a task with this function.

Argument	Required or Optional	Value
range	Required	Refers to the range of cells you want to evaluate based on the specified criteria.
criteria	Required	Can be a number, expression, cell reference, text, or function that defines which cells to add.
[sum_range]	Optional	Specifies the actual cells to add if you need to combine cells other than those defined in the range argument.

COUNTIF Function

The formula or syntax for the COUNTIF function is as follows,

=COUNTIF(range, criteria)

You will require the following arguments to perform a task with this function.

Argument	Required/Optional	Explanation
range	Required	Indicates the range of cells where the function will apply the criteria for counting.
criteria	Required	Defines the condition or criteria for counting.

So, from the above explanation, you can see that the SUMIF function’s syntax contains an extra argument, which is the sum_range.

Let’s use the same data set and criteria to see how these functions produce different outputs:

Steps:

Look at the image below, which displays two separate fields showing the outputs of these functions.

To calculate the total sales for the criteria Marshmallow, insert the following SUMIF formula in cell D24:

=SUMIF(C5:C20,D22,D5:D20)

Press Enter, and the formula will add all cell values containing total sales that match the Marshmallow criteria, showing the summation as the result.

To count how many times the word Marshmallow appears in the given cell range, enter the following COUNTIF formula in cell D27:

=COUNTIF(C5:C20,D22)

Here’s a summary of the differences:

Factors for Comparison	SUMIF Function	COUNTIF Function
Argument Syntax	Requires an extra sum_range in the argument.	Does not require this extra argument.
Output	Adds a range of cells based on a special condition or criteria.	Counts the number of cells matching the given condition or criteria.