How to learn Excel functions, and look at Excel functions: SUM, SUMIF and SUMIFS
In the spreadsheet, which Excel function we should use depends on what results you want to get and the conditions of the source data you use.
(Duration: 6:14)
＜＜ Related Posts ＞＞
- Excel Absolute and Relative Cell References and Autofill Function【Data Analysis Excel Skill】
- TEXT Function, which makes dates and numbers easily read and understood【Excel Skills】
How can we learn Excel functions?
Hi, this is Mike Negami, Lean Sigma, Black Belt.
In my current project in the Mentoring Program, I talk with my client about Excel functions quite often and he asked me which functions to use.
Which function we should use depends on what results you want to get and the conditions of the source data you use.
By the way, if you have any questions about Excel or Excel functions, write them in the comment section below and I would like to help you.
For example, in a cell in the worksheet we made in my client’s project, we would like to get the total sales for last month. In the source data worksheet, there are daily sales by customer. Those columns are purchase date, customer name and sales amounts. Which Excel function should I use?
I’m often asked “How did you learn the Excel functions?” Regarding Excel, I’ve never studied it in books or classes. In fact, I learned it from Google. I’ll show you what I mean.
Utilization of Google to master Excel functions
In our example, we want to “get the total sales for last month”, so just type ‘excel’ and exactly what you need to find in Google such as: ‘get the total sales for last month’. Then you’ll get pages that will show what you’re looking for.
It’s possible to use the ‘SUMIF Function’ here, which you can use to get the total by a search condition. Those sites will tell you how to use those functions too.
The maximum amount of knowledge we can obtain as an individual is no match for computers and the Internet. These days, it’s more important to increase our search skills than to increase the amount of knowledge, so you should not only learn necessary knowledge but also think of how you can easily retrieve that when needed.
An Excel feature, Excel Function Explanation List
I’ll show you another way to learn Excel functions. Although this is a basic function of Excel, surprisingly, it’s not well known. In a cell, as you type ‘=’ and an Excel function, all functions that start with what you typed will appear. Look carefully, when selecting each function with your arrow key, each explanation comes up.
You know about the SUM Function, here it says, “Adds all the numbers in a range of cells” When selecting ‘SUMIF’, it says “Adds the cells specified by a given condition or criteria”. You can check what each function does with this. What are the ‘condition’ or ‘criteria’? For example, ‘only for last month’ or ‘only in certain regions’, etc., you can narrow your data down by those conditions.
Then, keep typing up to ‘(‘, then you’ll see an explanation of how to complete that formula. At first, you may have no idea what those explanations mean, but please get used to them so that you can use even vaguely remembered functions by using this method.
Let’s write a formula of the SUMIFS Function
In addition to the singular SUMIF, there is also the SUMIFS Function that contains a plural S. These three functions can calculate the sum of the numbers within the specified range. The singular SUMIF can literally have one search condition. On the other hand, the plural form, SUMIFS can have multiple search conditions. Therefore, I always use the SUMIFS Function. Let’s demonstrate the SUMIFS function right now.
Type ‘= SUMIFS(‘, then first ‘sum_range’ is in bold. Since we want to get the total sales for last month, click on the ‘H’ on top of Column H so that it selects to that entire column. Now, you enter a comma here, the next, ‘criteria_range1′ has changed to bold. With this, you can tell what you have to enter each time.
In our example, ‘Last Month’ is the search condition. Since Column C is the purchase date, we want to extract only the sales of the last month’s dates in this column. There are multiple ways to accomplish this. This time I used the work column, which is Column B.
By using another function, we converted the dates in Column C to months in Column B. Select Column B and put a comma, then ‘criteria1’ has become bold. I’ve typed text for the last month above beforehand so I’ll select it and close the parentheses, then it’s completed. The last month’s sales came up. After this, change the month here, then, sales for that month will come out accordingly.
Although there are numerous Excel functions out there, there are about a dozen that you need to know for your data analyses. I’ll make articles that explain these, so use and practice Excel functions many times. Overall, there is no better way to master them than that.
