This is my second post in the series "How to manage better using an Excel function" (see my post on the procv - search function). More and more I want to show you how much easier and more practical your life can be if you use an Excel spreadsheet correctly. This time, I will talk about the importance of SOMASE to better manage any area of your company.
This function is used to sum up values conditioned to criteria that you can choose. When you finish reading this post you will learn how to use the SOMASE Excel function for any business situation and may lower a worksheet with feedback on the SOMASE function.
To begin, let's start by looking at some examples of the use of SOMASE in business management:
1. Example of using SOMASE in a Customer Prospecting spreadsheet
Many companies have sales teams or areas responsible for revenue. In such cases, we can work with a commercial team that visits or receives visits from customers to negotiate the sale of a particular service. Let's take a look at our Customer Prospecting worksheet below:
There, you have a list of all contacts made with customers (column B). Note that these contacts could be visits from your customers at your company (if you have a Travel Agency for example) or visited customers (if you have a software solution or a consultancy). Obviously, all customers will have various information registered such as the responsible seller (column F), service offered (column H), values (column I) and what stage of the funnel the customer is in.
Now imagine that you have 10 salespeople prospecting customers and want to know the total value negotiated by each of them. Adding the values in your hand would be too boring and time consuming every time you wanted to access this information. That's why there is a SUM function (or SUMIF in English). With it, you can make sums of values automatically depending on the criteria you choose in the spreadsheet. In this case, we want to make the sum of all negotiated values conditioned to the name of the responsible seller. Let's see how it works:
In our spreadsheet we created a new tab (4. Months, Salespeople and Area) with a table with the salespeople's name in column G and the sum of the amount negotiated in column K as shown in the image above. From the list of salespeople, using the formula SOMASE, excel automatically adds the values of each salesperson in the Prospecting tab and takes all the corresponding information to the cells in column K. Let's see the result more closely:
So in this first example, to get the sum of the values, we use the construction of the formulas in this way:
- For the first seller:
= SOMASE ('3. Prospecting'! $ F $ 8: $ F $ 500;'4. Months, Vendors and Area '! G83. Prospecting '! $ I $ 8: $ I $ 500)
- For the second seller:
= SOMASE ('3. Prospecting'! $ F $ 8: $ F $ 500;'4. Months, Vendors and Area '! G93. Prospecting '! $ I $ 8: $ I $ 500)
Note that the 2 part of each formula (blue items) has different numbers for each item searched (G8 and G9). This is because this is the criterion used to condition the sum and in this case Excel receives a message of type: "Every time you see the value G8 (or G9, or etc. - which is exactly the name of the vendor) , you must add the corresponding value ".
You should also have noticed that the first and last part of the formula refer to the Prospecting tab. This is because the analyzed values are filled in on that tab.
As you have seen, the SOMASE function is practical and functional. It has a very useful variation that we will not cover in depth in this post, but which is worth knowing, which is the SOMASES (SUMIFS) function. It is exactly the same as SOMASE, except that the sum is conditioned to more than one criterion to happen. In our example, you could condition the sum of values to the seller's name (criterion 1) and to the fact that he was able to close the purchase (criterion 2), showing the sum of values actually sold.
Let's show another example of using SOMASE.
2. Example of using SOMASE in a Cash Flow spreadsheet
Now let's look at the example of a Cash Flow worksheet. Here you will find your company's receipts (receipts) and outflows (expenses). Here is the preview of your releases:
See that all items (income and expenses) have a series of data and information related to them as data (column B), value (column E and F) and status (column G). If you want to know the sum of all the values, conditioned to the criterion if the status is paid or not, simply use the function SOMASE (or SUMIF in English). Let's see what the spreadsheet should look like:
In our Cash Flow worksheet we created a new tab (6. Real Cash Flow) that already has a table ready with SOMASE formulas. From the use of the launch tabs, automatically, excel will make a conditional sum. If it is marked as paid, it makes the sum, otherwise it does not "pull" the value of the other flap. Let's see the result:
In this second example, to get the sum of months, we made the formulas like this:
- For the month of January:
= SOMASE ('3. Jan '!$ G $ 11: $ G $ 500; "Payment";'3. Jan '!$ E $ 11: $ E $ 500)
- For the month of February:
= SOMASE ('3. Feb '!$ G $ 11: $ G $ 500; "Payment";'3. Feb '!$ E $ 11: $ E $ 500)
In this case, each month is in a flap, and every month has an identical structure. Because of this, the formulas are exactly the same, except for the references to the months (blue parts).
You could still use the Excel SOMASE formula to do analyzes and sums for virtually any list of values you have. For example, you can make sums of income or expense groups, cost centers, or any other criteria you want. Below I showed an image of the conditional sum of types of household expenses with the SOMASE formula in our personal finance spreadsheet (this data is pulled from another flap):
3. Understanding the SOMASE function conceptually in Excel:
What is written when you enter the SOMASE (conditioned sum) function is SOMASE (range, criteria; [sum_range])
interval: It's the icell range where data analysis will be performed. Here you should have the list where you will send Excel to look for your criteria. In our examples, it was columns F (example 1 - Prospecting) and column G (example 2 - Cash Flow). If you want to know the sum of the salesperson 1 values, your range should be the column where you record the salespeople's name.
criterion: This is the reference value to validate the condition that you set. They may exist in the form of a number, expression, cell reference, text or function. For example, the criteria can be expressed as 32 (number), “Jardel Souza” (expression), B5 (cell), etc.. In our examples, the criteria were the sellers' names (example 1) and the expression “Paid” (example 2)
Note: Any criteria for text or logical and mathematical symbols must be enclosed in double quotes ("). If the criteria are numeric, double quotation marks are not required..
sum_soma: This is the range of values that will be summed if the corresponding field of the "range" item is the same as the criteria. Did it look confusing? But it is not ... see the image below.
If we use the following formula = SOMASE (A1: A19; "House"; B1: B19), then we can see that column A has the values of my range and that my criterion is "House". So every time that “House” appears in column A, the value of column B corresponding to the row will be added. In this case, the values of lines 2 (R $ 600), 8 (R $ 1500) and 15 (R $ 120) must be added together, since in lines 2, 8 and 15 column A presents the criterion "Housing".
4. Now we will see the step by step in the practice of how to use SOMASE
To do the step by step in the practice of how to set up the SOMASE function in your spreadsheet, I will use a spreadsheet from scratch as if it were my personal expenses spreadsheet, but think of it only as the general structure to apply in your excel spreadsheet.
You will need to have a table structure, with at least 2 columns (in ours I put 3 just for you to see that this will not generate any problem). One of them will be its interval and the other the interval of sum. As we are talking about a function that will add values conditionally, it is important that its sum range has numbers (can be quantity, monetary values, etc).
To be less abstract, let's see the table in our spreadsheet that was zeroed and now has 3 columns: the first with spending items, the second with my personal cost centers (housing, health, transportation, etc.) and the third with the amounts that I hypothetically spent in the last month:
Any table that you have with at least 2 columns (being one of values and another of criteria analysis) can be used for the function SUMM.
2. Inserting the formula
Now that you have organized the structure of your spreadsheet, you need to create a new table (which can be done in a new tab or not) to insert the SOMASE function. To be more visual, I will create on the same tab as our main table, see:
See that in our structure, I selected 5 different criteria. For each criterion, we will insert a SOMASE function exactly the same as the others, but with different search criteria. Now that we have the structure, we can insert the function. For this, you will choose the cell where you want to add it. Let's start with the sum of values for the Housing cost (cell F5).
In this cell we will put the formula = SOMASE (B2: B19; E5; C2: C19), as shown below:
In this formula, we can see that:
- interval = B2: B19 - which is exactly the classification list of personal cost centers
- critérios = E5 - which represents the expression “House”. If you wanted, you could add just the expression, no problem
- sum_soma - C2: C19 - this is the list of values that will be added, whenever the corresponding line has the criterion in question
If you prefer, use the formula wizard. To do this, Click the tab Formulas> Insert function> SOMASE. There, you will have these same 3 fields to fill:
When filling in the formula correctly and pressing the enter key, see that the correct sum value will automatically appear:
3. The best way to use
If you continue to apply the formula SOMASE to the other cells (Health, Food, Transportation and Entertainment) we will have the table completely filled with all the information:
Using SOMASE excluding a criterion only
If you want, you can add all cells within a given range by excluding only one value that you are not interested in. For this, you need to use an exception command: <> : means except - in this case, the other values except those that are included in this criterion.
Using our example above, we might want to know how much we spend except for entertainment. For this, we will use the function: = SOMASE (B2: B19; ”<> Entertainment”; C2: C19), where it will be added all the values, except those corresponding to the entertainment criterion.
Give your opinion and suggestions
What other Excel formulas or functionalities would you like to learn more about? Leave a comment for me that I'm sure will answer.
Test your knowledge
Download one now SOMASE function worksheet with feedback.
We have some ready-made spreadsheets that use the formula SOMASE:
1) Get a quote