How to Use the SOMASE Excel Function (SUMIF)

135
51205
detail of the formula somase in excel
Excel Spreadsheets

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:

Sales prospecting worksheet - sales funnel flap

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:

Results and indicators of the sales funnel using the SOMASE function in excel

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:

Table of indicators using the formula somase in excel

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:

Cash flow using the excel formula somase

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:

actual cash flow table using the excel formula somase

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:

formula in the cash flow table

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).

Other Applications

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):

use of the somase formula in the personal finance worksheet

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])

detail of the formula somase in excel

Where:

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)

NoteAny 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.

simple table for use of the formula somase in excel

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.

1. Structure

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:

table with 3 columns for use of the formula somase in excel

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:

table created for use of the formula somase in excel

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).

table with specific use of the formula somase

In this cell we will put the formula = SOMASE (B2: B19; E5; C2: C19), as shown below:

add somase in detail

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:

Help window for creating formula somase in excel

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:

table with somase formulas

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

2) Advanced Cash Flow

3) Customer Prospecting

Excel Spreadsheets

135 COMMENTS

  1. Here is an example: I have 305 items each with their respective code, 7 worksheets corresponding to the days of the week. On the 7th day I need to take stock of what came in, came out, and what you have in stock to put in another folder. Then we add the similar items that came out on different days to take stock.
    I tried to do it for Somase and Procv but the formula is too big and ends up giving an error. Is it possible that when adding the output of a certain item it appears in that balance sheet adding up with all other similar items of the day, and the subsequent days?

  2. Good afternoon,
    I am trying to create a sheet with some formulas and I am in doubt with one, if someone can help me I appreciate it.
    So the situation is as follows:
    I want to sum up the number of times the word "Sale" and the word "Sale / Purchase" appear in a given cell.
    I use the formula {= SUM (IF ((F5: F102 = ”Sale”) + (F5: F102 = ”Purchase”); 1; 0))}, but #Value! appears.

  3. Rafael, good morning!

    I have two spreadsheets one with the data of the projects and totals of overtime to 50 and 100% the other worksheet is a synthetic, where I put the sum of everything.
    I imagine that the formula would be or SOMASES but it is not working. What data would you have in the formula?

    Thank you!

  4. Hi Paul, you can use a conditional function SE for whenever the value is negative, the absolute value or something similar appears

  5. Good afternoon, I need help on a time bank worksheet, where I work with the balance of the previous month that will be a cell of data entry, it happens that it does not accept typing negative hours, eg -23: 40 hours, and this me giving the biggest headache, someone can help, thank you.

  6. Hi Matheus, in this case you can only use a SOMASES with two criteria, one would be the type and the other the month

  7. January
    _______
    Type
    13 gray
    08 gray
    08 gray
    Brown 01

    Doubt: Rafael, good morning. I am trying to collect the values ​​of the two Ashes that I have in my spreadsheet in SOMASES since it is “such” Month of the year, but I am not succeeding. I tried to put a SOMASES inside another SOMASES, but it is not working.
    Could you help me?

    Big hug

  8. Hi William, you need to use the MONTH function in an auxiliary column to determine the month of the specific date and, when making the SOMA conditional to the month, use that auxiliary column as the base of the conditioned sum

  9. Rafael, good night.

    I'm trying to assemble formula. I will try to explain briefly.
    I have two plans
    1 - on the lines are the months and in columns my fixed expenses, 1 of the columns is to add the daily expenses of the 2 worksheet so that whenever it puts an expense in the 2 worksheet, it already some automatic in the worksheet 1
    2- with daily expenses with 2 columns, date of expenditure and value.
    I want him to consider, for ex. (spreadsheet 1 in the monthly spending cell the sum of the values ​​in column B of the 2 worksheet if it is within 1 given month)
    Ex; every expense that I had in May of the days 01 to 31 it adds to column B even though it has other expenses of other months in the same column.

    I'm being beaten up by when excel sets “May-2018” for example, the cell has a date value as 01/05/02018. therefore, if I have spent on 02/05/2018 in spreadsheet 2, it does not add up.

    I do not know if I was clear, but I hope you understood and can help me.

    I have tried to use it, if procv but it is difficult to complete the reasoning.

    Big hug and hope you see and can respond.

  10. Hi Rafael, I have a spreadsheet with two columns: Result (where I enter Approved, Disapproved, or Use in State), in the other column I have the Reason (Quantities Missing, Delayed or Returned). There are lines where the item in the 1 column is Approved for Missing Quantities Reason. However in another line, another item may have been Disapproved for the Missing Quantity Motif as well. In this way, I need a formula that adds the Missing Quantities items being Approved and also the items with Missing Quantities, however, Disapproved. Can you help me? Right away, thank you.

  11. Hi Dayanne, you must use an IF function similar to = SE (A1 <> “”; right answer; wrong answer)

  12. Hi Rafael, I need a formula for the teachers' diaries I work at, where I just need Excel to calculate one cell only if the other is filled.

  13. Hi Tiago, you can use the filter (just add it by the DATA tab) or you can create an additional column to analyze the dates of the last purchase from the customers and check the ones that are above 90 days without buying

  14. Good evening,

    I have a spreadsheet with the following data; SOCIAL REASON, ORDER VALUE, PURCHASE DATE.
    Data is from the last 12 months. I want to know how I filter CUSTOMERS THAT WE DO NOT BUY MORE THAN 90 DAYS. thank you

  15. Hi Jose, I've never tried to make color-related counting rules (although it's possible via VBA, it's quite complex). Typically, if a color is applied conditionally, this is for a specific value or text. This same condition must be the trigger for the count or sum of the values

  16. Good evening Rafael;
    I need a help like a worksheet in which I need to count the number of cells that receive a specific color by conditional formatting, but this conditional formatting is generated by a new rule with a formula hence I can not do color counting when formatting simple conditional I can do the count with a rule in vba.

  17. Hi Roberta, in that case you may need to use SOMASES, which does sums with more than one condition. When you want to add in an interval, you need to have the condition> 200 AND the condition <500 for example. It would look like:
    =SOMASES(Plan1!A4:A100;Plan1!E4:E100;">0″;Plan1!E4:E100;”<400")

  18. OLA RAFAEL, I WOULD LIKE A HELP I HAVE A LITTLE KNOWLEDGE IN EXCEL, I WANT TO DO SOMETHING SIMPLE TYPE OF 200,00 TO 500,00 FROM 500,01 TO 700,00 FROM 700,01 TO 900,00.
    I WANT THE FORMULA ALREADY BREAKED MY HEAD AND I CANNOT GET WHAT I NEED TO ADD THIS ON WORKSHEET 1 SEE IF I AM INO CORRECTLY, BECAUSE THE VALUE = SOMASE (Plan1! E4: E100; "> 00,01 ″) - SOMASE (Plan1! E4: E100; ”> 400,00 ″)
    I FEEL THAT I'M DOING SOMETHING WRONG BUT I CAN NOT IDENTIFY THE ERROR

  19. Hi Pablo, I can only think of a logic using an auxiliary column so that you use the SUM for each cell and then make the conditioned sum to this new column

  20. Rafael, I want to make a somase (s) [without auxiliary cells] and I would like to know if it is possible.
    I need to add the numbers in a column only if there are numbers also in the neighboring column (which can have number or text). It would be something for the somase to recognize if the value is number and then add up. It's possible?

    I've tried something SOMASE (H8: H14; UNCLE (H8: H14)) but NOT recognizes intervals.

    Thank you.

  21. Hi Paulo, you can record a macro for this, where, at the push of a button you have the value of the total added to a cell in another tab. This cell may have accumulated totals without problem. One caution you must have is not to push the button before the total has been accumulated if it can not have a compilation error.

  22. Hi Rafael. I need your help.

    I'm doing a spreadsheet and would like a TOTAL in a daily worksheet (deleted daily) to be saved in another worksheet in the same folder, accumulating the daily totals.
    Is this help possible?
    Thank you in advance.
    Paulo Sobrinho.

  23. I believe the error is in the range of SUM (the last argument of the function) that just repeats the table where it may have written VNS or not. I would do a test by putting 100 there to see if it works

  24. I need help?
    I have this table = SUMIF (B3: O30; ”VNS”; B3: O30)
    every time “VNS” appears I want it to add $ 100.
    How to do this??

  25. I was also testing and I managed to leave the formula in the cell. Ex. = SOMASE (C36: C38; C45), where the content of C45 is "> 100". I used the CONCATENATE function to transform the value 100 into “> 100”. It worked! Thanks for listening!

  26. Hi Fabio, I did some tests here and I really could not identify why this error might be occurring. One possibility is to use the same integer. If your table is dynamic, you may have to resort to some other functionality

  27. Good Morning!
    When I inform the criteria that I want to add values ​​greater than 100 = SOMASE (C36: C38; ”> 100 ″) the formula calculates correctly, now if the 100 is in a cell (C45) and I want to refer to it = SOMASE (C36: C38 ; ”> C45 ″) does not work. What am I doing wrong?

  28. Hi Marcello, how are you?

    In this case you have two conditions, you need to use SOMASES and, for the month, I suggest you make an auxiliary column with the month formula and, in the criterion, use the 1 value, which refers to the month of January

  29. Good night!! Supposing that I need to add sales for a given month ... how could the formula be for adding the criteria of: "Sales 1", "Dates" (FOR ALL DATES COO RESPONDING TO THE MONTH OF JANUARY) and the "Values" ??

  30. I did not want to have to create this column any more, but thanks for the guidance!

  31. Hi Samuel,

    if you only have one month in your worksheet, just use the SUM function. If you have more than one month, for the interval you will use an extra column, which should contain the MONTH function using the date reference. And in the criterion you will put the number for the analyzed month.

  32. Good Morning! a doubt, I have a spreadsheet with values ​​to pay and its dates, I wanted to make a table with all the amounts to pay per month, I believe that it is sum if it suits me, in the interval I select the dates of the accounts, in the interval_soma I put the values ​​of each account, but I do not know what information I put as criteria, no information I put was successful.

  33. Hi, thanks for the tip, it wasn't really that helpful, I found the formula and gave me another idea for my spreadsheet. I wanted the value of the month to appear in the auxiliary column only if there was some value in B to count how many releases came out in the month in which case it would be = IF ($ b1 = ””; ””; MONTH ($ a1)), thank you very much I got through your idea to improve another formula that I was using which was very extensive. Thanks!

  34. Hi Fábio, in that case I suggest you create an auxiliary column (could be column C with the formula MONTH).

    Then just create a table with the months from January to December and for each month create a conditioned counting formula. In practice, it should be like this = CONT.SE (C1: C100; 1) for January

    Then just change the 1 value by 2, 3, 4, to 12 for the other months

  35. Almost this, lacked the value if the condition is false.

    = IF (B1 = ”OK”; A3-A1, A3)

  36. Good morning, I would like to learn how in the second example more using the direct month in the formula and not the column example example:

    ——A ——————- B ——-
    01 / 01 / 2017 R $ 100,00
    05 / 01 / 2017 R $ 80,00
    04 / 02 / 2017 R $ 120,00
    I would like to count the values ​​per month example two sales in January the result would be 2 and February 1 I am trying to count by month independent of the value of column B I have another column C with the formula MONTH indicating it in front.

  37. Thanks for your help Rafael, I hadn’t thought about doing it that way…
    Following his logic would look something like this:
    SE (B1 = ”OK”; A3-A1)?
    In theory I understood, but at the time of thinking of a logical form I do not know how to proceed.

  38. Hi Anderson,

    you will not be able to create formulas or functions that are color-conditioned, but there is a way to do this that you said. Instead of thinking about coloring a cell, consider creating a formatting conditioned to the value of another cell. For example, B1 and, this cell may either stay white or have ok written. You can create a conditional formatting in A1 whenever B1 is OK to turn green and create a function in A3 so when B1 is OK it drops the value of A1.

  39. Hi Anderson,

    you will not be able to create formulas or functions that are color-conditioned, but there is a way to do this that you said. Instead of thinking about coloring a cell, consider creating a formatting conditioned to the value of another cell. For example, B1 and, this cell may either stay white or have ok written. You can create a conditional formatting in A1 whenever B1 is OK to turn green and create a function in A3 so when B1 is OK it drops the value of A1.

  40. Good morning guys.

    Let's see if anyone can help me ...
    I have a spending spreadsheet and would like to know if there is a way to put some formula in the total amount when I make the payment for the account, for example:

    I have an X value in cell A1 and a Y in cell A2, the sum of these values ​​is in A3 which would be my total.

    Is there a way to put a formula together with a formatting when I put the A1 cell in green color, drop the value of the A3 cell?

    I hope you can understand my logic rs'
    Thanks, I hope anyone can help me.

  41. Friends, I have a spreadsheet with 100 titles to pay with varied values, however I have to choose which titles I will pay to give the sum of up to 1500,00, how do Excel show me which titles are added to 1500,00?

  42. Hi Valeria, the defined criteria can be a written value or a reference. You can for example create a table with the values ​​you want and use the reference of each to have an automated worksheet instead of having one with only the written values

  43. Hello, how are you?
    Great blog and your teaching! Thank you for sharing so much knowledge.

    I am creating a cash flow spreadsheet and have had problem with my formula SOMASE.
    I used a list to discriminate what the entry or exit is about, and from that I made some formulas, but SOMASE using, for example, the criterion “check by sight”, which is part of the predetermined list, does not work.

    When I do it with the same criteria, but without choosing it from a list, the formula works. Is there a way to do SOMASE using a list criteria? Or should I type it every time it is “check”, “boleto” or something else?

    I don't know if I managed to be clear, but I hope you understand and help me ...
    Valeria

  44. Hi Ze, in this case you need to use the SOMASES function and add more conditions

  45. Good afternoon. using the function: ”= SOMASE (B2: B19;” Entertainment ”; C2: C19)”, how can I add a calculation column badly ?, for example in addition to looking in cells C2: C19, also looking in cells D2: D19. I've tried form C2: D19 and it doesn't add up.
    Thank you

  46. It is possible, yes, Leo, just organize a logic for it.

    In this case, I think it is best to have the B20 cell with the conditional SE (B2 = 3; B20 + 1; 0)

    If you have other rules, there are other ways of doing this logic

  47. Hi good night. It is possible to use this tool in the following situation: If field B2 = "3", 1 is added in cell B20, if it is not zero.

  48. The worksheet is currently this way. I wondered if there was any way to eliminate this auxiliary column. Maybe you use the left function inside the somase function. I'll try that way to see if it works.
    Thank you for your help.

  49. Hi Gabi, I imagine you need to separate these texts, but I do not think it's difficult. I thought of an auxiliary column with the LEFT function. So whenever this function returns the subtotal value it would add the corresponding column of values, generating exactly the result you want

  50. Good Leonardo, thanks for your participation and willingness to help others who may have the same doubt. If you need more help, just talk.

  51. Good afternoon.
    I have a doubt.
    I already use the SOMASE function on several worksheets, but I wonder if I can use it to sum cell values ​​starting with a given text.
    For example: I have a spreadsheet with several expenses separated by categories, at the end of each category I have a SUBTOTAL line of CATEGORY 1. So to make the sum of the total expenses, I wanted to make a SOMASE that searches for cells that start with SUBTOTAL, regardless of the subsequent text. It is possible? Or would the only solution be to separate these texts?
    Thank you.

  52. Responding to myself and helping others, yes it is possible.

    If (and (a5A1; C5; D5) (I put the formula somases in c5 and d5, to create the green and false value.

    If vdd -> SOMASES ('PAYING'! H: H; 'PAYING'! B: B; A5; 'PAYING'! L: L; C2) -> column C

    If false -> = SOMASES ('PAYING'! K: K; 'PAYING'! B: B; A5; 'PAYING'! L: L; C2) -> ColumnD

    We will:

    = IF (A5> $ A $ 1; SOMASES ('PAYING'! H: H; 'PAYING'! B: B; A5; 'PAYING'! L: L; $ C $ 2); SOMASES ('PAYING' '! K: K;' PAYING '! B: B; A5;' PAYING '! L: L; $ C $ 2))

  53. Is it possible to merge the two SE + Somases formulas (as true and false criteria) ????

  54. Hello,
    I want to use the SE formula, merged to somases, that is.
    If a1A1); (SOMASES ('PAYING'! H: H; 'PAYING'! B: B; A5; 'PAYING'! L: L; C2)); (SOMASES ('PAYING'! K: k; 'PAYING'! B: B; A5; 'PAYING'! L: L; C2))
    But it is not working.
    Can you help me ... Remembering that If a5> a1 (it's about date).

  55. Hi Vanessa, this account can be made using CONT.SE, but how will it be done in the worksheet will depend on how it is organized for the postings of those deliveries

  56. Hi Ryan,

    I always recommend using tabs in the same worksheet, since when using different workbooks you may have problems with the link. As I do not have much custom of using different worksheets, I do not know if the problem is only with the SOMASE

  57. HELLO!
    I AM SELLING GAS AND WATER, AND I WOULD LIKE ALL THE SALE THAT THE DELIVERER MAKES THE GENERAL QUANTITY OF SALE BY PRODUCT OBEYING THE ORDER OF DELIVERY. FOR EXAMPLE:
    CUSTOMER TO… .2 WATERS
    CLIENT B… .1 WATER AND 1 GAS OF 13KG
    CUSTOMER C… .1 GAS OF 13GH…

    AND AT THE FINAL REPORT AS MANY WATERS AND HOW MANY GAS OF 13 KG WERE SOLD OBEYING THE ORDER OF DELIVERY.

  58. Hello guy,

    My problem is the following, I am adding values ​​from other worksheets, and when these are closed appears the error "VALUE" in the cell, so I have been reading in some forums this is a peculiarity of SOMASE, but I do not have ctz, could it help?

  59. Hi Vinicius, assuming your answers are from line 1 to line 100, just use = CONT.SE (C1: C100; ”True”)

  60. Hello I need help ...

    in column A I have my answers
    in column B I have the correct answers
    in column C I have caused excel to compare column A with column B and the value of true (if both answers are equal) and false (if they are different)
    but now at the end of column C I would like to give you the amount of "real" that exist, so I would not have to count line by line. Anyone who can help me I thank you very much.

    thank you

  61. Hi Leonardo, it is quite possible and I imagine you need to use SOMASES with more than one condition. However there are some cautions you need to have:

    1 - the structure of the spreadsheet must be identical to the structure of the app

    2 - when pasting the data, you have to be careful to keep the formulas working. In this case the ideal is to have the structure ready with the same number of lines (or more) that you have in your app and simply paste values ​​from the app directly into the spreadsheet

  62. Hello,

    I need help. I have an application where I record my expenses, export to Excel and use it as a database to follow better. But I'd like to leave it automated, where I just need to export the database from the mobile to the spreadsheet. I tried to make formulas, but it always makes mistakes.

    I wanted to do with these conditions: if in the database tab column A is = the reference month of the C3 columns up to the N3 of the Budget tab, and if the F column category of the database is equal to the category of the Good column of budget tab, search for a value greater than 0 in that row of columns G and H in the database, and return to my budget tab. It's possible? What formula should I use?

    I thank you for your attention.

    Leonardo Pedrozo

  63. I would like to insert the following formula in a spreadsheet: If the value of line C2 is less than that of line C3 = return “0”; now, if the value of line C2 is greater than that of line C3 = do the subtraction of C2-C3. How can I create this argument?

  64. Hi Fernando, I really did not quite understand the difficulty you are having.

    You said you want to pull from each tab what was the investment, to consolidate on a table every month, correct? If so, in the first column you use a SOMASE by pulling the investment range, defining what is the value you want to pull and the sum range.

    If you have more than one condition, the process is similar, but using SOMASES.

  65. I could not put more than one interval and sum range in the same formula. I only managed to put every month in the same tab, but it gives a lot of work.

  66. Hi Fernando, how are you?

    In this case, where you have more than one condition, you need to use SOMASES

    If it is the sum of several months and SOMASE already solve, you can do SOMASE + SOMASE + SOMASE and so on

  67. Good morning Rafael, I am not able to put more than one interval in the formula, I have 12 tabs for the months, I want to pull from each tab how much I spent on “investment”, for example. Could you help me?

  68. Hi Grandson, I do not quite understand, but if you want to add the items that appear 1 and those that appear 0, just use the function SOMASE

    to the logo of this post we showed how to use it if you have doubts about how to apply

  69. Good afternoon folks,

    I have a worksheet that has a column with a binary result (1 or 0) for 0 hit and for 1 errors. Now I want to add this column. But I can not. According to the model in the image.

    The formula in the column is the following: = SE (E2 = 0; ”0 ″; SE (E20;” 1 ″))

    I had two remedies:

    1º write the numbers in a column next to it (if it is to put manual, for which the SE formula?)

    2ºSelect by selecting the cells manually (but if they are many items this becomes impracticable)

    How can I add the column numbers with the SE formula?
    Well, I need to have a percentage of items with no stock differences.

    Thank you

    Net

  70. Hi Breno, I believe you can do this using a = SOMASE (A1: A1000; ”E”; B1: B1000) -SOMASE (A1: A1000; ”S”; B1: B1000)

  71. Good Morning!

    I need a little help.

    I have a sheet of moving in and out of products ..

    In a table, some data such as:

    Column 1 Column 2 (qtd) Total Stock: ??
    E (input) 10
    S (output) 5

    I would like to know how to make a formula that, depending on the type (column 1, "E" or "S"), represents the quantity of final stock, and that quantity column can increase according to purchases and sales.

    Thanks in advance.

  72. Hi, Natalia, how are you?

    Just use the SOMASES function on the corresponding tabs. In the November tab you would have to create the SOMASES conditioning for the payment date to be October, with payment on 30 days

  73. I need a help: I ​​have in a spreadsheet the customer data; value service; form of payment In the form of payment column I can choose 30 or 60 days.
    How do I after choosing 30 or 60 days this data (client; value service) automatically go to corresponding tab?

    Example:
    Aba (October)
    Antonio; R $ 1.200; 30 days
    It will automatically repeat itself on the tab (November)

    can you help me?

  74. Hi Rafael, thank you very much, I tried to do this and it was giving me an error, and my brother-in-law helped me, I inserted the "parenthesis" before the formula and it worked.

    Ex: = (SOMASES (formula)) / 3

    Thanks, hugs.

  75. Hi Lucas, how are you?

    the division sign in Excel is the forward slash (“/”). So just write = SOMASES (formula) / 3

    in this case I made a SOMASES (not put the arguments) divided by the number 3. That simple. Hugs

  76. Hi Zenilda, everything good?

    An Excel spreadsheet is extremely customizable. So you can use it to do just about anything you want. Use to describe expenses, to place values, specific items, revenues, and so on ...

    In the same way, you can make any chart that has interest, as long as you have enough data and information to do so.

  77. Hi, good afternoon, I have some doubts and I would like you to help me in this matter: can a spreadsheet only be used to describe expenses? Another doubt, can graphics be provided according to our need?

  78. Good afternoon, I wanted to know after entering the function of somases, as I would to insert a division together.

    I have withdrawn the productivity of the team and need to divide by hour / month worked in sequence.

    Can you help me, follow function below:

    = SOMASES ('DATABASE'! $ L: $ L; 'DATABASE'! $ BF: $ BF; F $ 7; 'DATABASE'! $ V: $ V; ”FINISHED”) + SOMASES (' DATABASE '! $ L: $ L;' DATABASE '! $ BF: $ BF; F $ 7;' DATABASE '! $ V: $ V; ”REGISTRATION COMPLETED”)

  79. Thanks for the reference Jonatas! If you have some other content that you want to learn and do not have here, just talk

  80. Thank you already saved the skin here !!!

    Very good site and all articles !!! Congratulations, hand !!!

  81. It is quite possible, yes, although it does not seem like the best logic to me. I suggest that in this case you create an auxiliary column and in cells that want to do some logical testing, use the SE function. For example:

    = SE (B2 = ”PG”; 1, 0)

    Then it uses the normal SOMASE, using as criteria the 1 values ​​of the auxiliary column.

  82. Good afternoon Rafael, very good the post!

    My question would be to use specific cells instead of the colon for range, is it possible !?

    For example, Instead of SOMASE (B2: B19; ”PG”; C2: 19)

    If instead of adding from B2 to B19, I just wanted to specify B2, B5, B10 and B19….

    Thank you!

  83. In that case you probably have another column stating whether it's an entry or an exit, right? If it does not, the ideal would be to have, so you can use a SOMASE conditioned to the column that informs if it is an entry or exit.

    If you continue with doubt just tell me or put a picture of the worksheet here (easier than sending me the worksheet) ok? Hugs

  84. I have used your blog to make some spreadsheets, it has helped me a lot. But I'm having a hard time inserting a certain formula, I tried everything but I can not. Would you help me ?

    The idea is to get sums from the following information:
    total input value
    total output value
    and this information is in the same column.

    I want to know how I should proceed. if you help I can send you my worksheet so that you understand better.

    I thank

  85. Thank you Jesus,

    you really keep an eye on everything =]. I have already corrected the row error by column

  86. Hello Rafael, I just wanted to say that it's not B-line, but column B. Lines are horizontal in a table

  87. Hi Alysson,

    if you want to make SOMASE from column B + column C, you need to do something like

    = SOMASE store 1 + SOMASE store 2

    This is because SOMASE only fetches a column to add, so if you want to add more than one column you can do 2 functions and then add or, as I indicated, do the addition of one result with the other in the same cell

    Hugs

  88. HELLO, I SET UP A SPREADSHEET TO KNOW THE TOTAL VALUE OF EACH SUPPLIER, SELL EACH STORE I HAVE, THE SOMASE FUNCTION SEEMS TO BE APPROPRIATED, BUT I CANNOT RELATE SOMA_INTERVALOS FOR THE TWO COLUMNES WILL EXAMPLE: “B” STORE 1 AND COLUMN “C” STORE 2. WHAT I WANT TO KNOW IS THE TOTAL OF THE SUPPLIER “CHICÓ” SELL STORE 1 AND 2. I HOPE TO BE WELL SPECIFIC, AND THANK YOU FOR HELP.

  89. In fact you just create a macro Rafael.

    I developed an Advanced Excel course that explains exactly how to do this in the macro part. If you are interested, it has a nice price and is very complete - https://cursos.luz.vc/course/curso-de-excel-2013-avancado/

    it's a bit difficult to explain in writing, but anyway, trying to explain more, you create a list with your 60 products and a table for input form. In this table you enter a field to record the material and quantity. Then create the DB and the macro at the end, which will always pick up the value entered, which can be any of the 60 items.

    At the end you will still have to create a table with SOMASE and CONT.SE to count your stock from the DB okay?

  90. Could you give me an example? I do not understand,
    I work here in the company with more than 60 products, I just need to launch them as we manufacture, will I have to do 1 macro for each? and how do I add the macro to what is already in the formula?

  91. Hi Rafael,

    to do this automatically you need to use a macro and create a database. Basically the macro should copy the value you entered, just paste the value into the database, add a row in the database and delete the value entered.

    So, whenever you add new materials, they are being registered in the DB okay?

  92. Can you tell me if you can keep the result of the formula in the cell without removing it?
    I need to make a stock, assuming
    Material:
    Qty:
    in lapis lapis material, and in 5 qty, then I want the result to be played in a database, so when I launch 3 + lapis, it appears in the 7 stock and does not change the 5 3 pello

  93. Hi Sérgio, it's quite true that we use SOMASE in columns, because that's how it usually appears in most cases in real life, but you can use it to add row values.

    The logic is the same. You need to have two lines with the same size, for example A1: Z1 (with values) and another B1: Z1 with the criteria you want to search. In this case, it will always look up the criteria specified in row B and add the corresponding value in row A.

  94. All that shows of SOMASE is to sum values ​​organized by lines, and sum the values ​​of the lines in the indicated column.
    I can not get it to work to add, on the contrary, values ​​allocated in columns, that add up the values ​​of the columns that meet the criterion, in the line indicated.

    Do you know if there is another way?

  95. Hi Alisson,

    I didn't really understand what you meant. You will need to be a little more specific ...

  96. good afternoon rafael, do you have any formula that helps you identify several specific values ​​in a column, knowing only the sum of them?

  97. Hi Magu,

    you can use the SOMASE or SOMASES function on that other worksheet (tab) by searching the values ​​in the general worksheet.

    The process is quite simple. In the extra worksheet, in the cell where you want the result of the total of each vendor write function SOMASE

    = SOMASE (value column, vendor name, column with vendors name)

    I have written extensively, but there in function you need to put the references of the general worksheet ok?

  98. Good morning Rafael….
    I would like to know how I create a worksheet where the data to be transferred is from another worksheet. Eg in the General Plan I have all the sales made, in this worksheet I have sellers name, dates of issue, expiration dates, values ​​etc. But I would like to create another spreadsheet (tab) where I would pull the information as total from each vendor. how do I do?

  99. Alessandro,

    in general I would run into a good one, but I'm really stuck with work and things to do.

    I recommend that you hire someone who understands Excel to help you with this challenge. There's a new platform where you can find this help - http://planilhaspersonalizadas.com.br/

  100. IF I SENT YOU THE WORKSHEET YOU WOULD GIVE A LOOK AND VERY IF YOU CAN HELP ME

  101. Hi Alessandro, I find the report you want to generate very complex,

    it is likely that you will be able to do using SOMASES, CONT.SES and most likely INDIRECT to automate searches on different worksheets, but I think I would give the dog a job to do.

    I still keep with the perception that using dynamic tables will help you a lot. In Excel 2013 you can merge data from several different worksheets into a single PivotTable if I'm not mistaken. From there it would be a matter of adjusting the report to stay the way you want.

  102. GOOD AFTERNOON, I HAVE 31 SHEETS WITH EACH RESPECTIVELY FOR ONE DAY OF THE MONTH AND WITHIN I HAVE THE CLIENT'S NAME, QUANTITY OF PRODUCTS, UNIT VALUE. AND TOTAL VALUE AND THE SELLER'S NAME… I REGISTER THE SALES BY THE SELLER'S NAME. AI I NEED AT THE END OF THE MONTH TO ASSEMBLE THE REPORT ON SEPARATE SHEETS DISTINCTLY WITH THE NAMES OF THE CUSTOMERS, QUANTITY, UNIT VALUE AND THE TOTAL VALUE OF THE PRODUCT DATE BY DATE FOR A CERTAIN SELLER, KNOW WHOM HE WAS SELLING, AMOUNT, AMOUNT UNIT OF THE PRODUCT, TOTAL AMOUNT AND COMMISSION… FOR HE TO KNOW WHO THEIR CUSTOMERS WERE AND THEIR RESPECTIVE PURCHASES AND DATES OF THE RESPECTIVE PURCHASES….

  103. Hi Alessandro,

    I believe you do not need any formula. This is a result that you can easily view with a PivotTable.

    Simply insert the PivotTable and move the fields to their respective areas.

  104. GOOD EVENING, I HAVE A SALES SCHEDULE WHERE I PLACE CUSTOMER'S NAME, QUANTITY, UNIT VALUE, TOTAL VALUE AND NAME OF SELLER RESPONSIBLE FOR CUSTOMER. I NEED A FORMULA THAT READS TO ME THE NAMES OF CUSTOMERS OF A DEALER SELLER AND THE VALUE OF ITS RESPECTIVE SALE. WOULD YOU HELP ME?

  105. Now I understand,

    I imagine you get this result with CONT.SES. It would be something like:

    =CONT.SES(E12:E31;”PAULO”;K12:K31;”0″”

    If that function doesn't work, just create an auxiliary column on the side of column K (of dates) with the following function = SE (K2 = ””; ””; 1). after that, just use the following function:

    =CONT.SES(E12:E31;”PAULO”;K12:K31;1)

  106. Hi Rafael! So I do not really want to add the dates but I want to add the cells that have some date, that is if in a column 10 has rows and in five rows has some date filled my result is 5.

    In the example below, PAULO has 2 cells with dates filled out. This is what I want in my formula, which counts how many cells filled by some date has determined responsible.

    EK
    JOSÉ 05 / 06 / 2015
    MARIA 06 / 07 / 2015
    PAUL
    JOSÉ 24 / 05 / 2015
    PAULO 30 / 04 / 2015
    MARIA
    PAULO 01 / 06 / 2015
    MARIA
    JOSÉ 21 / 02 / 2015
    PAUL

    I got it with a crazy formula here. If you have any that are more practical, please tell me!

    = SUM (IF (SE ('PROJ. REPORT ACCOMP. GETEC'! $ E $ 12: $ E $ 31 = ”PAULO”; 1; 0) * IF (ISNUM ('PROJ. REPORT ACCOMP. GETEC'! $ K $ 12: $ K $ 31); 1; 0) = 1; 1; 0))

    Thanks for the answer!!

  107. Hi Paul, how are you?

    Do you want to add values ​​related to a specific date or do you want to add the value of the dates?

    Because it seems to me that adding dates does not make much sense. For example, adding 02 / 03 / 2015 with 05 / 04 / 2015 will give a value that does not represent anything.

    Can you explain exactly what you want to add?

    Just to get ahead, if it's the same amount spent, you need to use SOMASES, because in this case you will have 2 conditions, the NAME and the DATA. It may be necessary to create an auxiliary column (as I suggested for Jeferson here below)

  108. Hi, Jeferson, is everything okay?

    You can do this by creating an auxiliary column next to the date column and using the MONTH function in that column. For the 5 line, you would do something like = MONTH (A5).

    If the date were 12 / 03 / 2015, the result of the month function would be 3. So you create SOMASE for each month in this way:

    =SOMASE(B5:B1000;3;G5:G1000)

    In this case, the argument is 3, that is, it will pull the sum of the values ​​when the date is the month of March.

  109. Good Morning! What I'm trying to do is this:

    In column C I have the names of those responsible for an activity and in column K I have the date of completion of the activities. What I want is to add the completed activities of a given person (which are those with dates filled). That is, I want to add the completed activities of PAULO, for example, by adding the cells of column K that are filled when in the same row PAULO is written in column C.

    I tried this formula but it is counting dates and not filled cells.

    = SOMASE ('PROJECT REPORT ACCOMP. GETEC'! $ E $ 12: $ E $ 18; ”PAULO”; 'PROJECT REPORT ACCOMP. GETEC'! $ K $ 12: $ K $ 18)
    Could you help me, please?
    Congratulations on the work! Great!

  110. Good morning Rafael, first of all my congratulations for the work, really adds a lot in the administrative routine of all, I have a doubt and I know that you can help me.

    I have a spreadsheet on a profit-posting table.
    - Table A5 starts the launch dates
    - Table G5 counts the corresponding amount of profit per day

    I would somehow like that I could add in a cell the corresponding profits by MES,
    ex SOMA TOTAL OF PROFITS
    MAY = 2.000
    JUNE = 5000

    Can you help me ?

  111. Hi Kelly,

    surely you are putting some argument in the wrong way. Because if 2 works with arguments, it should work with as many arguments as you'd like.

    Let's do it by steps. Try to check these items:

    - check if the column with dates is formatted as a date or as a number (for the case we are talking about, it needs to be formatted as a number - 1, 2, 3, etc.)

    - check that you are putting the arguments in the correct locations (sum range and criteria range)

    - if you are dragging the formula, make sure you put the absolute references ($) in the right places. In this case, the correct thing would be something like:

    =SOMASES($A$2:$A$1000;$B$2:$B$1000;”1″;$B$2:$B$1000;”2″;$B$2:$B$1000;”3″;$C$2:$C$1000;”boleto”)

    - Make a test also taking the quotes out of the numbers, thinking now, I believe it works with and without quotes, but it is worth the test

  112. Hi Rafa! Thank you for the brief return! However for me it is not working when I put more than two criteria for example a date, in numeral or date format, including zeroing the cell value… but if you put only two criteria, like date 03/06 and boleto, it brings the value … Could you give me a suggestion? ABC

  113. Hi Kelly, how are you?

    We have a Cash Flow spreadsheet with Financial Projection that has these features ready. If you are interested, this is the link - https://luz.vc/planilhas-avancadas/planilha-de-fluxo-de-caixa-avancado-com-projecao-financeira-3-0

    If you prefer to make the formula, there is not much mystery, it would be something like:

    =SOMASES(A2:A1000;B2:B1000;”1″;B2:B1000;”2″;B2:B1000;”3″;C2:C1000;”boleto”)

    assuming:

    - column A has the values
    - column B has the dates (in number), 1, 2, 3. If it has a date with a date format it will not read this function that way
    - column C has the form of payment

  114. Hello Rafael, how can I mount the somases formula, to define the sum of the values ​​of a cash flow, taking into account the type of the receipt / payment and the date? Example, add the ticket receipts on 1,2 and 3 days? Abces

  115. Hi Fernando, sorry for the delay in answering, but no notification has come to me of this doubt.

    In this case, you must use the conditional function E together with the SOMASE. It would look something like this:

    = SOMASE (range [this is the range of criteria, where there are 3-digit numbers]; criterion, where you will insert the function (E (> 99; <200); range sum [which is the column of values ​​you want to add]

    I think it will work out that way. anything tell me

  116. Just put

    =COUNTIFS(P3:P50000,<AB03,A3:A50000,"Noble Houston Colbert")

    I did not log into Excel to verify, but I believe it will work this way

  117. I would like to please a tip about the formula, = COUNTIFS (P3: P50000, ”<01-apr-2014", A3: A50000, "Noble Houston Colbert"), instead of having a specific date value, (01- apr-2014, I would like to refer to lower values ​​(<) the cell AB03, how do I replace the date with the cell in the COUNTIF function?

  118. Rafael,
    Thanks for the excellent post, very informative and helpful.
    In my cash flow, I'm categorizing my expenses with 3 digits codes instead of names. If I wanted to add up all the expenses of a certain range, for example all expenses classified between 100 and 199, which are in this case my administrative expenses, how would I report this interval in the formula's criterion field?

LEAVE AN ANSWER

Please, write your comment
Please enter your name