Google Sheets Functions – COUNTIF(S) & SUMIF(S)

In this post, we’re going to look at how we can count things and add things up depending on certain conditions that we set.

There are four functions we’ll look at here: COUNTIF, SUMIF, COUNTIFS, and SUMIFS. Here I’ll show you the various aspects of the functions through some practical examples.

---

Example 1 – COUNTIF: Counting the number of instances of a specific number

In this example, and to introduce COUNTIF, we have a list of part numbers of some products and we want to know how many parts we have that are numbered “123”.

In cell B2, we write the following formula:

=COUNTIF(A2:A16,123)

There are two parts, first you state the range of values you want to look at, then in the second part, you state what you want to look for. So, in this case, we’re looking in cells A2 to A16 and we are looking for the number 123. As it looks down the row, every time it finds the number 123, it adds it to the count. So, in this example, it found 6 instances of the number 123.

---

Example 2 – COUNTIF: Counting how many values are over a certain number

We can also use COUNTIF to count how many values are greater than or less than a particular number. In fact, it can check against pretty much any type of mathematical condition, an example of which we’ll see later.

Here we want to see how many students got over 60 in a test, maybe to divide the group into a stronger one and a weaker one.

In cell C2, as before we start off with the COUNTIF function, set the range we want to look at, in this case it’s B2 to B9, then set the condition to look for any tests over 60. Notice the syntax of the condition. When we use the operators, like greater than or less than, we have to wrap them up in speech marks.

=COUNTIF(B2:B9,”>60″)

And correctly, it’s found 5 students with a test result of more than 60.

---

Example 3 – COUNTIF: Counting the number of times a word or phrase appears in a range

This time we’re going to look for a piece of text. We have a book inventory and we want to know how many books called “English File” we have.

In cell C2, we write the following formula:

=COUNTIF(A2:A12,“English File”)

Like the operators above, text needs to be in speech marks. Here it’s found 6 books called English File.

---

Example 4 – COUNTIF: Using a cell reference to find instances of whatever has been written in that cell

So far, we’ve stated the condition within the formula but often we want a flexible formula that can look for any condition we type in a particular cell.

Here we have the same book inventory but this time we give the user the option to type in the book they are looking for in cell C1. Then in cell C2 it will tell them how many books we have of that name.

In the condition part, the important part and the part where most problems occur, is the syntax here. First, we wrap the equals operator up in speech marks like we saw in example 2. Then to refer to a cell we need to first add the ampersand (&) then the cell reference.

Whatever we type in cell C1, the COUNTIF will look for in the range, making this far more flexible than stating it within the formula.

=COUNTIF(A2:A12,“=”&C1)

For those who have read my chapter in the Sheets Functions book on CONCATENATE, this is one reason why I use CONCATENATE rather than the ampersand to join cells together, as the ampersand has different uses and can be a bit confusing if you have more complicated formulas with the ampersands doing different jobs.

---

Example 5 – COUNTIF: Using wildcards to look for broader values

Here we have a list of classes and we want to know how many junior classes we have. The problem is that, there are different levels of Juniors, 1, 2, and 3, so our formulas so far wouldn’t be able to find all these different ones. That’s where a wildcard comes in.

In cell B2, we type the following formula:

=COUNTIF(A2:A14,”Junior*”)

The key part here, is the use of the asterisk (*) after the word Junior. This allows it to look for anything that starts with the word Junior and it doesn’t matter what comes afterwards. Hence it will count the number of times there is a class starting with the word Junior. In this case there are 8.

The wildcard can also be used before the word, for example, you could look for all the classes in level 1, by typing “*1”, which would look for anything ending in 1.

---

Example 6 – COUNTIF: Counting dates

The final example of COUNTIF, shows us that we can also count the number of times a date appears. For example, here we want to know how many appointments we have on 17/10/2016.

Dates like text, need to be in speech marks. It’s also important to match the format. I usually state the day, month and year, even if the dates in the table are formatted in a different way. I.e. if the data just showed the day and month, the formula would still work.

=COUNTIF(A2:A15,”17/10/2016″)

---

Example 7 – SUMIF: Adding up the number of products sold on a particular date

Now let’s look at SUMIF. This is similar to COUNTIF but the syntax can be slightly different as quite often we’re not looking at just one range.

Here we have some products that have been sold on different days and we have how many of each product has been sold. What we want to know is how many products did we sell on a particular date, e.g. 17/10. In cell E1, the user types in the date they want to find the information for, then in cell E2, it will tell us how many have been sold.

In the brackets, we first type the range where we’re looking for the date, e.g. A2 to A12. Then we add the criteria, in this case whatever’s in cell E1 (the date). Then for SUMIF, we add a third part, this is the range which contains the values we going to sum, e.g. quantities sold (‘sum range’).

=SUMIF(A2:A12,E1,C2:C12)

So in our example, it’s found 3 dates that equal the 17/10 and summed up the quantities sold on those dates, i.e. 100+300+600 = 1,000.

---

Example 8 – SUMIFS: To find the quantity sold within a date range

Similar to SUMIF, we have the function SUMIFS, which uses more than one criteria before adding up. Here we have the same information as before, but this time we want to find out how many products we’ve sold between two dates (17/10 to 19/10).

Important: The syntax here is different from SUMIF, and let’s go through it step by step.

=SUMIFS(C2:C12,A2:A12,“>=”&E1,A2:A12,“<=”&E2)

We start with the range we want to get the values from, i.e. the sum range (C2:C12).

Then we state the range we want to find the dates in, i.e. A2 to A12.

Then the first criteria we want to use, i.e. the date is greater than or equal to the date in cell E1, in this case, 17/10/2016 (“>=”&E1). As before the operator is in speech marks, and the cell reference has an ampersand before it.

Then we add the second condition. In this example, the range is the same, i.e. A2:A12.

Then we want to look for dates less than or equal to the date in cell E2, in this case, 19/10/2016 (“<=”&E2).

It then adds up the products sold between those dates, 100+300+150+200+600+200+300+450+100 = 2,400.

It looks a fairly complicated formula but it really just has three parts:

sum range, criteria 1 (range & criteria), criteria 2 (range & criteria)

We can add more criteria if we want just by adding another range and criteria on the end.

---

Example 9 – COUNTIF: Counting how many students are above average

Returning back to COUNTIF, we can use other functions as the criteria within the COUNTIF one.

Here we want to see how many students got over the average mark in an exam. Normally, we would have to find the average of all the marks, then see which marks were above that figure. Well, that’s exactly what the COUNTIF function can do for you.

In the second part, we want to know what marks are greater than the average of all the marks in that range. So, we start off with the greater than operator in speech marks, then like the cell referencing we need to add an ampersand before the function AVERAGE. Then we add the range we want to average.

=COUNTIF(B2:B9,“>”&AVERAGE(B2:B9))

The average was 69%, and so correctly it’s found that 4 students got an above average mark.

---

Example 10 – COUNTIFS: Highlighting duplicate rows

In this final example, we can use the COUNTIFS function in conjunction with conditional formatting, to highlight rows on our sheet which have duplicate information.

Here we have a list of classes, their timetables and teachers. Some of the rows are duplicates and we can use COUNTIFS to find the duplicates. We add a fourth column to record if the row is duplicate or not.

In column D we add the following formula:

=COUNTIFS($A$2:$A$13,$A2,$B$2:$B$13,$B2,$C$2:$C$13,$C2)>1

It works by looking in the 3 columns A, B, and C and sees if there is more than 1 occurrence of the contents of that row. So taking the first row (row 2) as an example:

It checks to see if the content of cell A2, B2, and C2 occurs more than once in the rows below, which it does, i.e. in row 6. So, it states that it’s TRUE that it occurs more than once, in other words, it’s duplicated.

If we look at row 3, we can see that this time that the combination of those three pieces of information are not repeated in the other rows. So, it states that it’s FALSE that it occurs more than once.

We could leave it like that and if we wanted to remove the duplicates or edit them, we just look for those rows which have TRUE in them. However, to make it easier to find them, we can colour the rows to highlight them.

Having selected the contents of the table (range A2:A13), right-click and select “Conditional Formatting…” from the menu.

This opens the Conditional format rules sidebar menu. Click on the drop down menu, which by default says “Cell is not empty”.

Then scroll down to the bottom of the menu and select “Custom formula is”.

Here type in the formula below:

=$D2=TRUE

Click OK and this adds the conditional format rule to the range.

This checks to see if the value in column D is TRUE. If it is, it adds the formatting to the whole line.

If you want to know how this works better, read my post on Conditional Formatting.

---