How to Use SUMIF in Google Sheets [User Guide]

How to use SUMIF in Google Sheets 37

In this article we will learn about How to use SUMIF in Google Sheets with examples. This is one of the best guide that you can follow to learn most of the common applications of SUMIF function in Google Sheets.

What is SUMIF in Google Sheets?

Google Sheets is used to store data and information for the user and provide calculations on that data according to the requirements of users. Some complex computations may take a lot of time of users if processed manually. For the ease of users, Google Sheets provide a lot of formulae for the computational purposes so that users need not to waste time upon the calculations and rather spend that time on analyzing and utilizing that computation. SUMIF is such a function in Google Sheets which allows us to compute the SUM function based on some conditions.

SUMIF is a basic composition of SUM and IF functions of Google Sheets. SUM is used to add up numbers while IF is used to specify a condition for a certain event. Here, in SUMIF function, purpose of both SUM and IF functions are brought together in order to make a better function. SUMIF function is used to “sum” up the numbers “if” they hold a specific condition.

See also  How to Add Subscripts and Superscripts in Google Sheets (Easy Methods)

Why we use SUMIF in Google Sheets?

There are various problems in real life that require summation only if a certain condition holds true. Let us discuss some examples for such situations. Let us suppose we have the following worksheet:

How to use SUMIF in Google Sheets 1

We may require SUMIF function for performing various functions on the above data. Let us consider the following scenarios:

  • We may want to know quantity of a certain product that was sold. E.g. how many Lavender Oil were sold in the above record? In order to do this manually, we are required to look at the product name or product number of Lavender Oil and sum up the quantity.
  • For the same product, we may be interested in finding out how much of the total revenue does it make? Or the percentage of revenue it contributes in total revenue?
  • We may also want to find the total amount ($) sold in a specific month say June. We can look up the date from the Time column and search for a specific word “June” and ignore the rest of the date using SUMIF function.
  • Or we may want to find the total amount sold to a specific customer say “Tony”. We can look up “Tony” in Customer Name Column or find customer Id in Customer ID column and add up the amount.
  • We may want to find out the total quantity and amount ($) sold by a specific Salesman. We can look up the salesman in Seller Id column.
  • We may also want to add up the amount ($) for amounts greater than 10$ in order to find more about the behavior of transactions.

Let us demonstrate how to use SUMIF under various circumstances as described above and even more in Google Sheets below.

See also  Conditional Formatting based on Another Cell in Google Sheets

Syntax of SUMIF in Google Sheets

Basic syntax and composition of SUMIF function goes as:

=SUMIF (range_of_cells, condition, [sum_range])

Let us understand each part of the above formula separately as:

  • “=” implies the beginning of the formula in a cell.
  • “SUMIF” is the keyword we are using in order to imply that we are going to use SUMIF function of Google Sheets.
  • “(” implies the start of function.
  • “range_of_cells” contains the cells range which are tested for the condition specified in “condition”.
  • “Condition” contains the condition that will be tested to ensure the sum.
  • [sum_range] specifies the range of cells that will be added up i.e. summed up if condition holds true. It is optional.
  • “)” implies the end of function.

This is the basic syntax of SUMIF function in Google Sheets along with the meaning of keywords used in it. The same syntax can be used to solve many real-life problems in Google Sheets.

See also  How to Use Slicer in Google Sheets [Mini Dashboard Template]

How to use SUMIF without [sum_range] in Google Sheets

As the third parameter provided to SUMIF function is optional i.e. [sum_range]. We may omit the sum_range and still use the SUMIF function in the same way. The only difference it makes to the whole function is that the same range_of_cells are checked for a condition and added up if the condition is true. It can be used in the cases like add up a range of cells if it is greater than a specific amount or smaller.

Let us take a short example here. Suppose out of range B4:B9, we want to add up the cells if their values are less than 3. We will use the SUMIF as:

=SUMIF(B4:B9, “<3”)

This formula would add up the cells from B4 to B9 for the cells that contain values less than 3.

How to use SUMIF in Google Sheets

It is very easy to put checks on the data cells before using them for summation using SUMIF function. Let us briefly describe the use of SUMIF for different occasions and circumstances with examples.

How to use SUMIF for null value condition in Google Sheets

Let us start by a basic and frequently occurred condition in Google Sheets; that is null cell.

Suppose you want to find out the sum of specific items for null values. For example, find the sum of amount or price for null Customers (in case, customers were not recorded while entering the transactions). Although there is no record of customer on those transactions, it just means that transactions were successful but customer’s records are missing. So, we need to find out the revenue generated which is not under the label of any specific customer and may also result in a shaded transaction history.

Suppose we have the following worksheet:

How to use SUMIF in Google Sheets 2

Here we see 3 rows of null cells for Customer record, now we need to add up the amounts ($) for null cells in the Transaction Record History. Let us find it out using the following method:

Step 1: Prepare for writing the formula with SUMIF function in it.

We will make a Analytics heading and then mention what are we finding on one cell and finally our formula on the cell next to it so that reader can understand what is the purpose of  formula and what does it do.

We have the table for Analytics as:

How to use SUMIF in Google Sheets 3

Now, we can write down the formula in the next cell.

Step 2: Start writing the formula in the cell.

Let us first analyze what should be the formula here. Here we are supposed to add up the amounts of the cells which have the Customer null. So we will use the Customer Name or Customer Id column for range_of_cells and Amount column for Sum_Range. Let us start by starting the formula as:

=SUMIF(

How to use SUMIF in Google Sheets 4

We can see as we write SUMIF, Google Sheets automatically provide its basic syntax for the ease of user.

Step 3: Specify the range of cells that will be used for condition in the formula either by mentioning their cell numbers or select them by dragging the mouse over them as:

How to use SUMIF in Google Sheets 5

Step 4: Write down the condition separating by comma. In this case, we need to get the cells with null values, so we will use “” as out condition.

How to use SUMIF in Google Sheets 6

We can see that Google Sheets will keep prompting the syntax of function and also mention to us which part of the syntax we are currently writing. As in the above case, criterion is highlighted as we are on criterion (condition) part.

Step 5: Now, specify the range of cells separated by a comma that are to be added together if condition is true. We can write them down according to the cell numbers or select them as in earlier case as:

How to use SUMIF in Google Sheets 7

Now, our formula is almost complete using the SUMIF function, we can also see the to-be result (output) around the cell.

Step 6 (Optional): End the formula with “)”.

“)” is indicating the end of SUMIF function. Even if user does not explicitly put “)” to indicate the end of function, Google Sheets still recognize that function is complete as the syntax is being followed.

Finally, our formula looks like this:

How to use SUMIF in Google Sheets 8

After pressing enter after entering the formula, we have the output as:

How to use SUMIF in Google Sheets 9

Which shows the total amount in $ with null Customer Name and null Customer Id.

See also  How to Strikethrough on Google Sheets [4 Methods]

How to use SUMIF for number value condition in Google Sheets

Suppose you want to find out the sum of amount ($) sold by a specific salesman or each salesman. For the purpose, we can use the seller ID column to find out about each seller. We can use the same SUMIF function with a little modification.

A number condition may contain many conditions such as fixed number, > condition or < condition, etc. It all depends upon the nature of problem that we are dealing with, to choose the condition.

Let us use the same worksheet again. But now range_of_cells are comprised on Seller ID column and we have to sum up amounts for a certain Seller ID as:

How to use SUMIF in Google Sheets 10

Let us find sales by salesman with ID “3782” as:

How to use SUMIF in Google Sheets 11

We will use Seller ID to determine if the amount is to be summed up. Let us find out the amount sold by 3782 using the following method:

Step 1: Make preparations for writing the formula with SUMIF function in it.

We already Analytics heading for storing our analysis gained by using SUMIF function. Let us add another row into it as:

How to use SUMIF in Google Sheets 12

Now, we are ready to write the formula.

Step 2: Start the formula in the cell next to title.

Let us start the formula as:

=SUMIF(

You will see that Google Sheets will show the syntax of SUMIF function for the ease of user as:

How to use SUMIF in Google Sheets 13

Step 3: Specify the range of cells in the SUMIF function.

You can specify the range either by writing the cell range in the SUMIF or you can just select the cells that make up the range and Google Sheets will automatically write their cell numbers range as:

How to use SUMIF in Google Sheets 14

Step 4: Put in the condition after comma.

After mentioning range, put a comma and specify the condition.

In this case, we need to get a specific Seller ID to match exactly in order to satisfy the condition. So, we will just write the Seller ID 3782 as:

How to use SUMIF in Google Sheets 15

Step 5: Specify the range of cells to be added separated by a comma. These cells are added up if condition is true. Here, we need to add amount ($). We can write them down according to the cell numbers or select them as in earlier case as:

How to use SUMIF in Google Sheets 16

Now, our formula is almost complete using the SUMIF function, we can also see the to-be result (output) around the cell.

Step 6 (Optional): End the formula with “)”.

“)” indicates the end of any function but it is optional in Google Sheets as if the syntax is recognizable by Google Sheets; it will automatically put ) as soon as ENTER key is pressed or cell is unselected.

Finally, the formula is as:

How to use SUMIF in Google Sheets 17

We can see result as:

How to use SUMIF in Google Sheets 18

It shows the total amount in $ sold by 3782. We can also use the same procedure for finding amount ($) sold by each Seller as:

How to use SUMIF in Google Sheets 19

When dealing with numbers, there is no need to put quotation marks “” around the condition. In case of mathematical condition such as >10, it is written as “>10”.

See also  How to Stop Rounding Numbers in Google Sheets (2 Methods)

How to use SUMIF for text condition in Google Sheets

Google Sheets allow us to use SUMIF with various conditions. We may put in a text condition enclosed by quotation marks “”. There are various ways to use text conditions. We may be searching for cell with some fixed text or we may be searching for a part of text that may occur within the cell. If we are looking for the cell with the exact text, we will just write that text in “” marks as condition (similar to previous example). But if we are looking for a part of text in cells, we will use the method described below.

Let us find out the total amount of sales of “Face Products”. So, we are looking for a certain word “Face” in the start of product name and it includes all kinds of face products as:

How to use SUMIF in Google Sheets 20

Step 1: Select the cell where you want to write the formula with SUMIF function in it.

We are ready to write the formula here:

How to use SUMIF in Google Sheets 21

Step 2: Start writing the formula in the cell.

Let us start the formula in the same way as:

=SUMIF(

Google Sheets will show the syntax of SUMIF function for the ease of user as:

How to use SUMIF in Google Sheets 22

Step 3: Specify the range of cells in the SUMIF function.

Here, we are selecting product name column as:

How to use SUMIF in Google Sheets 23

Step 4: Write down the condition after comma.

After comma, we write our condition as start with “Face”. So, we will use the wildcard “*”. Condition goes like this:

How to use SUMIF in Google Sheets 24

Google Sheets mentions we are on criterion part of the SUMIF.

Step 5: Specify the range whose sum is to be found.

We need to sum up amounts so we’ll mention its range after comma as:

How to use SUMIF in Google Sheets 25

Finally, the formula becomes:

How to use SUMIF in Google Sheets 26

We can see results as:

How to use SUMIF in Google Sheets 27

It shows the total amount sold by Face Products.

See also  How to Use COUNTIF & COUNTIFS in Google Sheets

How to use SUMIF for two parameters in Google Sheets

As discussed above, the third parameter is optional. If we want to use the same cells that are checked for condition for summation, there is no need to write their range again. Rather range needs to be specifies only once.

Let us find the sum of amounts ($) sold that were above or equal to 10$ for the same worksheet.

Step 1: Select the cell where you want to write the formula with SUMIF function in it.

We are ready to write the formula here:

How to use SUMIF in Google Sheets 28

Step 2: Start writing the formula in the cell.

Let us start the formula as:

=SUMIF(

How to use SUMIF in Google Sheets 29

Step 3: Specify the range of cells in the SUMIF function.

You can specify the range either by writing the cell range in the SUMIF or you can just select the cells that make up the range and Google Sheets will automatically write their cell numbers range. Here, we are selecting Amount ($) column as:

How to use SUMIF in Google Sheets 30

Step 4: Write down the condition after comma.

After comma, we write our condition as greater than or equal to 10. Condition goes like this:

How to use SUMIF in Google Sheets 31

Google Sheets mentions we are on criterion part of the SUMIF and also the next portion of syntax appears in square brackets [] meaning that it is optional. So, we will just skip it and our formula is complete.

Step 5 (Optional): End the formula with “)”.

Finally, the formula becomes:

How to use SUMIF in Google Sheets 32

We can see results as:

How to use SUMIF in Google Sheets 33

It shows the total amount in $ that are greater or equal to 10$. We can also use the same procedure for solving similar types of problems which require only 2 parameters of SUMIF.

See also  How to Count Words in Google Sheets [4 Methods]

How to use multiple SUMIF functions in one cell in Google Sheets

Google Sheets provide us the facility to use multiple functions in one cell (to be precise, in one formula). We can use two or more SUMIF functions in one formula. Let us demonstrate the method by the help of an example.

Let us find the total amount ($) sold to Customer ID “2022” and Customer ID “2011” by using 2 SUMIF functions and add them up together. So this formula will naturally use 2 SUMIF functions. One for finding the sum of amounts sold to 2022 and another one for finding the sum of amounts sold to 2011.

We will use the same worksheet for demonstration.

Step 1: Select the cell where you want to write the formula with SUMIF functions and start writing the formula using Equal Sign “=”.

Let us prepare the title and start writing the formula as:

How to use SUMIF in Google Sheets 34

Step 2: Write the first SUMIF function.

Firstly, we will find sum of amounts sold to Customer ID “2022” using SUMIF function as:

How to use SUMIF in Google Sheets 35

Step 3: Add the separation after first SUMIF function.

We cannot write multiple functions in one formula (or cell) without specifying the relationship between them. Here, the two SUMIF functions are to be summed up, so the separation is +, it can also be -, /, *, etc.

How to use SUMIF in Google Sheets 36

Step 4: Write the next SUMIF function.

After separation, we can write the next SUMIF function.

In this SUMIF function, we will add up the amounts for Customer ID 2011 as:

How to use SUMIF in Google Sheets 37

Step 5: The complete formula is:

How to use SUMIF in Google Sheets 38

And we can see the output results as:

How to use SUMIF in Google Sheets 39

It shows the total amount bought by Customer ID 2022 and 2011 together. We can also use the same procedure for solving similar types of problems.

We may write more SUMIF functions in the formula separating those functions using separators and the same procedure.

Important Note: We must mention the end of all SUMIF functions coming between the formula by “)” to end it in order to start writing separation as well as the next SUMIF function. “)” is only recovered automatically by Google Sheets if it is missing at the end. Otherwise, the formula may result in an error.
See also  How to Change Cell Size in Google Sheets [3 Methods]

Download/Copy Google Sheets Workbook

Useful Notes

  • Always remember to put “=” sign in cell before using any formula or function. = indicates to the Google Sheets that a formula or function is coming up ahead. Without = sign, no formula or function would work.
  • All functions must be enclosed using brackets “()” after their keywords. “(“ indicates the start while “)” indicates the end of functions.
  • Parameters are always provided within ( ) “ for any function.
  • [] represents that this element is optional and may be skipped.
  • Always remember than any condition that involves a logical or mathematical statement must be written inside quotation marks (“”). If the condition is simply numeric, e.g. if the value is 3, then only 3 is written instead of “3” and quotation marks are omitted from the condition. Formula would be as: “=SUMIF(C3:C8,3)”. And in case of greater than 3, formula would be as: “=SUMIF(C3:C8, “>3”)”.

See also  How to Add/Create Drop Down List in Google Sheets

Frequently Asked Questions

Is SUMIF keyword case-sensitive in Google Sheets?

No, sumif keyword is not case sensitive at all. You may write sumif, Sumif, sUmIf, or any combination of upper and lower case characters. As long as it spells s+u+m+i+f, it will be considered as a function in Google Sheets.

Is SUMIF function condition (criterion) case-sensitive in Google Sheets?

No, conditions are not case-sensitive in Google Sheets. Any combination of upper and lower case are considered the same for a same word.

Does sumif also work instead of SUMIF?

Yes, as SUMIF keyword is not case-sensitive. You may use sumif rather than SUMIF.

Can SUMIF work without using equal sign “=” in the start?

No, SUMIF cannot work without using equal sign.

“=” indicates the start of any formula. Google Sheets provide plenty of functions for users with the help of keywords. Those keywords are recognized once the cell has started with = sign to indicate the start of formula. In simple words, functions can only be used inside formula.

Otherwise, function will appear as:

How to use SUMIF in Google Sheets 40

Is “)” mandatory for ending SUMIF function in Google Sheets?

No, although writing down “)” to specify the end of function is a good practice; it is not mandatory in Google Sheets if missing in the end of formula. Even if you forget to put “)” in the end of formula, Google Sheets will automatically put it in place as long as the syntax is complete.

Always remember that “)” is recovered by Google Sheets only if it is missing in the end of formula. If formula contains multiple parts or multiple functions and “)” is missing between the formula, it would not recover and you will get an error message as:

How to use SUMIF in Google Sheets 41

How to use SUMIF in Google Sheets 42

Can we use SUMIF function with only 2 parameters?

Yes, it is possible to use SUMIF function with 2 parameters rather than 3 parameters as the third parameter is optional. An example of this is shown above in article.

Can we use multiple SUMIF functions in one formula in Google Sheets?

Yes, we may use any number of SUMIF functions inside one formula in Google Sheets. Method is shown in above article.

Can we use SUMIF function inside other functions in Google Sheets?

Yes, we can use SUMIF functions inside other functions as well. But remember that SUMIF must act as a parameter of that function. For example, inside SUM function, many SUMIF functions can be given inside as its parameters.

See also  How to Use HLOOKUP Function in Google Sheets (User Guide)

Conclusion

Google Sheets provides us with tons of formula and functions. Users need not to waste their time on composing formulae or functions themselves rather they can just use the built-in Google Sheets functions and tools. One such function is SUMIF function. SUMIF provides combined features of both IF and SUM functions. It checks the conditions of IF and only if those conditions are satisfied, the satisfying cells will establish a SUM. Think about applying all the checks on cell values before calculating SUM, it is as extensive as it sounds.

In this article, we discussed the syntax of SUMIF function, its uses, performance and how to use the function under various circumstances and various kinds of problem statements.

Feel free to share your thoughts in the comment section below.

Thanks for Reading!

Content Protection by DMCA.com

Widad Khan

Hi, I am Widad Khan. I was born and raised in Pakistan. I have always loved the field of Computer Sciences and since I was a kid, I would say I wanna grow up to be a scientist, now this term is mostly related to "Computer Scientist" and not scientist alone. I am pursuing my Bachelor Degree in Computer Sciences from GCU, Lahore. I have always loved keeping records of different types of data. Earlier, I would use MS Excel for that. But with the advancements in Google Sheets, my work has become much easier. And also it's very easy to share and coordinate data with fellows and coordinates.

OfficeDemy.com
Logo
Enable registration in settings - general