How To Use The Basic SUMIF Function In Excel Explained
1.2 هزار بار بازدید -
11 ماه پیش
-
In this video we discuss
In this video we discuss how to use the basic SUMIF function or formula in excel. We go through a detailed example, and also cover using text in the function
Transcript/notes
The SUMIF function allows you to sum the values in a range of cells that meet a certain criteria that you choose. This is the basic text input for a SUMIF function or formula. An equals sign, the word SUMIF, then an open parenthesis, next is the range of cells, then a comma, then, in quotations, the criteria, and then a closed parenthesis.
Let’s go through an example. Here is a table I made up for an employee and the dollar amount of gift cards they sold for each day of the week.
We are going to use the SUMIF function to add up the dollar amount of sales they had, for days when their sales were $300 or greater. We are going to put the answer in cell A10, so, left click on cell A10 so it is highlighted. Next, we type in an equals sign, then we type in the word sumif, and a pop up box will appear with some options. We are going to left double click on the top option, SUMIF. Now, in our answer cell we have the word sumif then an open parenthesis, this is asking us to put some information in here.
We are going to type in the formula, then I will explain its parts in detail. First we have to input the range of cells we want into the function. We want cells B2 through B6, to do this, we are going to left click and hold on cell B2, then drag down to cell B6, and release the click and hold. Next, we type in a comma, and then we type in a quotation marks. From here, we type in a greater than symbol, then an equals sign. Next, we type in 300, then we type in another quotation marks, and then we type in a closed parenthesis.
So, in the parenthesis, there are 2 parts, we have the range of cells for the dollar amounts of gift cards sold each day, then in quotation marks we have the criteria, which in this case is $300 or more.
From here, we hit the enter key, and we have our output of 800, and by eyeing the table we can see this is correct, on Tuesday and Friday the sales were $300 or more, and adding them up, we have 500 plus 300, which equals 800.
This can also be used when dealing with text. In this table we have the item sold, the department and the amount of the sale. We are going to use the SUMIF function to add up the dollar amount of sales from the electronics department. We are going to put the answer in cell A27, so, left click on cell A27 so it is highlighted. Next, we type in an equals sign, then we type in the word sumif, and double click on sumif in the pop up box. Just like before, in our answer cell we have the word sumif then an open parenthesis, this is asking us to put some information in here.
First we have to input the range of cells into the function that we want the criteria to apply to. We want cells B18 through B23, to do this, we are going to left click and hold on cell B18, then drag down to cell B23, and release the click and hold. Next, we type in a comma, and then we type in a quotation marks. From here, rather than type in mathematical symbols, we type in the word electronics, then we type in another quotation marks. Now we are going to put in a third part for the function, the range of cells to sum if the criteria is met, which is cells C18 through C23. We left click and hold on cell C18, then drag down to cell C23, and release the click and hold. And then we type in a closed parenthesis.
What this is saying is add cell c18 if cell b18 has the text electronics in it, then add cell c19 if cell b19 has the text electronics in it, and so on.
From here, we hit the enter key, and we have our output of 375, and by eyeing the table we can see this is correct, as we have 125 plus 250, which equals 375.
We can also use this same method for sumif when dealing with numbers, as you see in this example, where the function is add values in column C if criteria is met in column B. I hit the enter key and we have an answer of 40.
Chapters/Timestamps
0:00 What is the SUMIF function in excel?
0:23 Example SUMIF function in excel
0:37 How to enter a SUMIF function in excel
2:03 How to use text in a SUMIF function in excel
3:53 How to add values in one column if criteria is met in a different column
Transcript/notes
The SUMIF function allows you to sum the values in a range of cells that meet a certain criteria that you choose. This is the basic text input for a SUMIF function or formula. An equals sign, the word SUMIF, then an open parenthesis, next is the range of cells, then a comma, then, in quotations, the criteria, and then a closed parenthesis.
Let’s go through an example. Here is a table I made up for an employee and the dollar amount of gift cards they sold for each day of the week.
We are going to use the SUMIF function to add up the dollar amount of sales they had, for days when their sales were $300 or greater. We are going to put the answer in cell A10, so, left click on cell A10 so it is highlighted. Next, we type in an equals sign, then we type in the word sumif, and a pop up box will appear with some options. We are going to left double click on the top option, SUMIF. Now, in our answer cell we have the word sumif then an open parenthesis, this is asking us to put some information in here.
We are going to type in the formula, then I will explain its parts in detail. First we have to input the range of cells we want into the function. We want cells B2 through B6, to do this, we are going to left click and hold on cell B2, then drag down to cell B6, and release the click and hold. Next, we type in a comma, and then we type in a quotation marks. From here, we type in a greater than symbol, then an equals sign. Next, we type in 300, then we type in another quotation marks, and then we type in a closed parenthesis.
So, in the parenthesis, there are 2 parts, we have the range of cells for the dollar amounts of gift cards sold each day, then in quotation marks we have the criteria, which in this case is $300 or more.
From here, we hit the enter key, and we have our output of 800, and by eyeing the table we can see this is correct, on Tuesday and Friday the sales were $300 or more, and adding them up, we have 500 plus 300, which equals 800.
This can also be used when dealing with text. In this table we have the item sold, the department and the amount of the sale. We are going to use the SUMIF function to add up the dollar amount of sales from the electronics department. We are going to put the answer in cell A27, so, left click on cell A27 so it is highlighted. Next, we type in an equals sign, then we type in the word sumif, and double click on sumif in the pop up box. Just like before, in our answer cell we have the word sumif then an open parenthesis, this is asking us to put some information in here.
First we have to input the range of cells into the function that we want the criteria to apply to. We want cells B18 through B23, to do this, we are going to left click and hold on cell B18, then drag down to cell B23, and release the click and hold. Next, we type in a comma, and then we type in a quotation marks. From here, rather than type in mathematical symbols, we type in the word electronics, then we type in another quotation marks. Now we are going to put in a third part for the function, the range of cells to sum if the criteria is met, which is cells C18 through C23. We left click and hold on cell C18, then drag down to cell C23, and release the click and hold. And then we type in a closed parenthesis.
What this is saying is add cell c18 if cell b18 has the text electronics in it, then add cell c19 if cell b19 has the text electronics in it, and so on.
From here, we hit the enter key, and we have our output of 375, and by eyeing the table we can see this is correct, as we have 125 plus 250, which equals 375.
We can also use this same method for sumif when dealing with numbers, as you see in this example, where the function is add values in column C if criteria is met in column B. I hit the enter key and we have an answer of 40.
Chapters/Timestamps
0:00 What is the SUMIF function in excel?
0:23 Example SUMIF function in excel
0:37 How to enter a SUMIF function in excel
2:03 How to use text in a SUMIF function in excel
3:53 How to add values in one column if criteria is met in a different column
11 ماه پیش
در تاریخ 1402/05/22 منتشر شده
است.
1,247
بـار بازدید شده