Md. Araf Bin Jayed
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.
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])
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)
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: How to Use COUNTIF Function In Excel to Count Bold Cells
Similar Readings
- How to Find Intersection of Two Lists in Excel (3 Easy Methods)
- How to Use COUNTIF Function with Array Criteria in Excel
- Count Text at Start with COUNTIF & LEFT Functions in Excel
- Excel COUNTIF to Count Cells Greater Than 1 (2 Examples)
- How to Use SUMIF, COUNTIF and AVERAGEIF Functions in Excel
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: How to Use Nested COUNTIF Function in Excel (6 Suitable Ways)
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
- [Fixed] COUNTIF Function with Wildcard Not Working in Excel
- COUNTIF Function to Count Cells That Are Not Equal to Zero
- Apply Excel COUNTIF with Pivot Table Calculated Field
- How to Use COUNTIF with WEEKDAY in Excel (2 Easy Methods)
- COUNTIF Date Is within 7 Days
- How to Use Excel COUNTIF That Does Not Contain Multiple Criteria
