Difference Between SUMIF and COUNTIF Functions in Excel

While working in Microsoft Excel, users have to use various types of functions for their working purposes. Some of these functions have many similarities, while others have many differences. You can differentiate them based on many aspects, like their syntax arguments, input criteria, or what value they show as an output. In this article, I will show you how to figure out the difference between the SUMIF and COUNTIF functions in Excel.


Download Practice Workbook

You can download the free Excel workbook here and practice on your own.


2 Ideal Examples to Figure Out the Differences Between SUMIF and COUNTIF Functions in Excel

In this article, you will see two ideal examples to figure out the difference between the SUMIF and COUNTIF functions in Excel. To show the difference, I will consider two aspects of these functions. One is their argument syntax, and the other is their output based on given criteria.

I will use the following sample data set to illustrate my article further.

2 Ideal Examples to Figure Out the Differences Between SUMIF and COUNTIF Functions in Excel


Example 1: Difference in Function Argument Syntax

The first example, based on which I will figure out the difference, is the argument syntax of both functions. What are the requirements that you need to fulfill to get the desired results from these functions? I will discuss it in the following section.

SUMIF Function

The syntax for the SUMIF function is as follows,

=SUMIF (range, criteria, [sum_range])

Syntax of SUMIF Function as An Ideal Example to Figure Out the Differences Between SUMIF and COUNTIF Functions in Excel

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

Argument Required or Optional Value
range Required Firstly, the range of cells that you want to evaluate by criteria.
criteria Required Secondly, the criteria are in the form of a number, an expression, a cell reference, text, or a function that defines which cells you want to add.
[sum_range] Optional Finally, 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)

Syntax of COUNTIF Function as An Ideal Example to Figure Out the Differences Between SUMIF and COUNTIF Functions in Excel

Again you will require the following arguments for getting outputs from this function.

Argument Required/Optional Explanation
range Required Firstly, the range of cells where the function will assign the criteria for counting.
criteria Required Then, the condition or criterion for the selected range of cells.

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

Read More: COUNTIF Excel Example (22 Examples)


Example 2: Difference in Showing Outputs

In this discussion, by using the same set of data and the same criteria, you will see how these functions show different outputs. In order to learn more about this, see the below-given steps.

Steps:

  • First of all, look at the following image where you can see two separate fields showing the outputs of these functions.
  • Here, two show the output, I will use Marshmallow as the criteria.

  • Secondly, insert the following SUMIF function formula in cell D24 to get the output.
=SUMIF(C5:C20,D22,D5:D20)

  • Thirdly, press Enter and you will see the above formula will add all the cell values containing total sales that match with the criteria Marshmallow and show the summation as a result.

  • Consequently, to show the output of the COUNTIF function, use the following formula in cell D27.
=COUNTIF(C5:C20,D22)

  • Finally, after pressing Enter, the formula will count how many times the Marshmallow word is present in the given cell range.

Read More: COUNTIF vs COUNTIFS in Excel (4 Examples)


Summary

From the above discussion, you can see all the differences in the following table.

Factors for Comparison SUMIF Function COUNTIF Function
Argument Syntax Firstly, it requires an extra sum_range in the argument. Contrarily, this function does not require this argument.
Output Secondly, the function adds a range of cells that you will specify with a special condition or criteria. Consequently, it counts the number of cells that matches the given condition or criteria.

Conclusion

That’s the end of this article. I hope you find this article helpful. After reading the above description, you will be able to figure out the difference between SUMIF and COUNTIF functions in Excel. Please share any further queries or recommendations with us in the comments section below.

The ExcelDemy team is always concerned about your preferences. Therefore, after commenting, please give us some moments to solve your issues, and we will reply to your queries with the best possible solutions.


Related Articles

Araf

Araf

I am Araf. I have completed my B.Sc in Industrial and Production Engineering from Ahsanullah University of Science and Technology. Currently I am working as an Excel & VBA Content Developer in Softeko. With proper guideline and aid of Softeko I want to be a flexible data analyst. With my acquired knowledge and hard work, I want to contribute to the overall growth of this organization.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo