How to Use the Combination of COUNTIF and SUMIF in Excel

Get FREE Advanced Excel Exercises with Solutions!

COUNTIF and SUMIF are two of the most effective and versatile functions in Excel.  The COUNTIF and SUMIF functions are attributed to conditional counting and summation respectively. These functions perform the task of SUM, COUNT, and IF functions. However, sometimes we need to use them together to get our desired results. In this article, we will show how to use COUNTIF and SUMIF functions in combination.


Download Practice Workbook

You can download the practice workbook here.


2 Examples to Use the Combination of COUNTIF and SUMIF in Excel

In this article, we will discuss 2 instances to use the COUNTIF and SUMIF functions in combination. In the first example, we will find out the average price of the products by combining the functions. Then, we will determine the number of products that have sales values less than a certain amount. The following image represents the dataset that we will use to show the application of COUNTIF and SUMIF functions together.

countif sumif combination


1. Determining Average Price

The COUNTIF function is a statistical function in Excel. It counts the number of cells that match a particular criterion. It returns a number as its output. The SUMIF function too belongs to the category of statistical functions. It will return the summation of cell values that fulfill a particular condition. In this method, we will use these functions to determine the average price of the products.

Steps:

  • Firstly, select the F5 cell and write down the following formula,
=SUMIF(B5:B15,"Apple",C5:C15)/COUNTIF($B$5:$B$15,"Apple")
  • Then, hit Enter.
  • As a result, we will get the average price of the item.

calculating average to show how to use countif and sumif in combination

  • Then, lower the cursor down to AutoFill the rest of the cells.

🔎 Formula Breakdown:

  • SUMIF(B5:B15,”Apple”,C5:C15): The SUMIF function will go through the B5:B15 range and look from “Apple”. Then, it will sum all the prices associated with “Apple” in the range C5:C15.In this case the summation will be 16.
  • COUNTIF($B$5:$B$15,”Apple”): The COUNTIF function will scan through the cells in the range B5:B15 and return the number of occurance of “Apple” in that cell range. Here, it will be 4.
  • SUMIF(B5:B15,”Apple”,C5:C15)/COUNTIF($B$5:$B$15,”Apple”): This one is simply the formula of average price. We have the total number of apples over the total price of the apples. In this instance, it will be $16 by 4 or $4.

Read More: How to Use COUNTIF for Non Contiguous Range in Excel


Similar Readings


2. Using COUNTIF and SUMIF with Condition

In this instance, we will apply an inequality condition in our formula which consists of the COUNTIF and the SUMIF functions. We will find out the number of items that have a total price less than $18.

Steps:

  • To begin with, select the E5 cell and write the following formula down,
=SUMPRODUCT((SUMIF(B5:B15,B5:B15,C5:C15)<18)/COUNTIF(B5:B15,B5:B15))
  • Then, hit the Enter button.
  • Consequently, we will get the number of items that have a total price less than $18.

Using COUNTIF and SUMIF with Condition

🔎 Formula Breakdown:

  • COUNTIF(B5:B15,B5:B15): This will return an array of values. The COUNTIF function will look for the number of occurrence of “Apple”, “Mango” and “Orange” as it moves from cell B5 to B15 and for each value in each cell it will look for number of occurrences of that value in the B5:B15 range and return the number. The result would be something like this : {4;4;4;3;4;3;4;3;4;4;4}.
  • SUMIF(B5:B15,B5:B15,C5:C15)<18): At first, the SUMIF function will look for “Apple”, “Mango” and “Orange” in each cell. For example, in the C5 cell, it will have Apple. So it will sum all the prices of Apple from the C5:C15 range and return $16. Then. it will sum for Mango and return $19.5 and then again it will sum for Apple and return $16 and it will do the same as it move from B5 to B15 and finally return an array like this : {16;19.5;16;14.8;19.5;14.8;16;14.8;16;19.5;19.5}. Then, it will evaluate the values if they are less than $18 or not. Then it will return an array of True and False like this: {True;False;True;True;False;True;True;True;True;False;False}.
  • SUMPRODUCT((SUMIF(B5:B15,B5:B15,C5:C15)<18)/COUNTIF(B5:B15,B5:B15)): Finally, the True values will be replaced by 1 and the False values with 0. Then, these array with 1’s and 0’s will be divided by the array returned by the COUNTIF function and will return an array like this: {0.25;0;0.25;0.333333;0;0.333333;0.25;0.333333;0.25;0;0}. Finally, the SUMPRODUCT function will sum these values and will return 2.

Read More: How to Use SUMIF, COUNTIF and AVERAGEIF Functions in Excel


Conclusion

In this article, we combined the COUNTIF and the SUMIF functions in a formula. Since, individually, the functions are very versatile, the combination of them allows users to perform different calculations in a robust way.  If you have any questions regarding this essay, feel free to let us know in the comments. Also, if you want to see more Excel content like this, please visit our website ExcelDemy.Com and unlock a great resource for Excel-related content.


Related Articles

Adnan Masruf
Adnan Masruf

I am an engineering graduate. I graduated from Bangladesh University of Engineering and Technology(BUET), one of the top universities in Bangladesh from department of Naval Architecture & Marine Engineering with a major in structure. I am an avid reader of fiction especially fantasy. I also keep myself abreast of the recent developments in science and technology. I believe diligence will eventually pay off and luck tends to favor those who work hard.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo