+ Reply to Thread
Results 1 to 26 of 26

SUMIF with multiple criteria in one column

  1. #1
    Registered User
    Join Date
    03-09-2020
    Location
    London, England
    MS-Off Ver
    2013
    Posts
    15

    SUMIF with multiple criteria in one column

    Hi all,

    I'm new to this forum but I'll try to explain my problem as clearly as possible.

    In the "Export value to the UK" column, I want to sum the values of exports to UK from "Other finance business services" and "Other business services n.i.e" for Austria.

    The issue is SUMIFS returns a value of zero when there is two or more criteria for the same column.

    The attached spreadsheet is a sample of the problem but ideally I'd like to replicate this with criteria of up to 10.

    If anyone knows any formulas I can use to achieve this, that'd be great.

    Thanks!
    Attached Files Attached Files

  2. #2
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,055

    Re: SUMIF with multiple criteria in one column

    First thing to look for, clean-up the spaces in A12 and A13.

    In C12 copied down
    =SUMIFS($D$2:$D$9,$A$2:$A$9,$A12,$B$2:$B$9,$B12)

    Also, should B12 and B13 be -- Other business services n.i.e.

    I get 97 for AT and 41 for BE
    HTH
    Regards, Jeff

  3. #3
    Valued Forum Contributor
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    779

    Re: SUMIF with multiple criteria in one column

    C12=IF($A12<>"",SUMIFS($D$2:$D$9,$B$2:$B$9,LEFT($B12,6)&"*",$A$2:$A$9,$A12),"")

    Copy down

    SUM Other finance business services and Other business services n.i.e. for AT e BE
    Last edited by CARACALLA; 03-10-2020 at 03:48 PM.

  4. #4
    Valued Forum Contributor Haluk's Avatar
    Join Date
    02-14-2019
    Location
    Turkey
    MS-Off Ver
    Office 2010 - 32 Bit
    Posts
    682

    Re: SUMIF with multiple criteria in one column

    After cleaning-up the spaces at the of the strings in A12 and A13, enter the below formula in cell C12;

    Please Login or Register  to view this content.
    You may need to change semi-colons (;) to commas (,) depending on your version of Excel.

    A working file is attached ...
    Attached Files Attached Files
    Last edited by Haluk; 03-10-2020 at 04:54 PM.

  5. #5
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    27,184

    Re: SUMIF with multiple criteria in one column

    ...another option is a different layout with a Pivot Table and some Slicers to analyse the data.

    See attached
    Attached Files Attached Files
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  6. #6
    Registered User
    Join Date
    03-09-2020
    Location
    London, England
    MS-Off Ver
    2013
    Posts
    15

    Re: SUMIF with multiple criteria in one column

    Thanks all very much for the help!

    I tried the formulas posted from jeffreybrown, CARACALLA and Haluk but they all returned zero? Richard Buttrey, the formulas don't add up the services numbers.

    To be clear, what I want to do is add "Other finance business services" + "Other business services n.i.e." for each country e.g Austria is 614 + 97.

  7. #7
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,055

    Re: SUMIF with multiple criteria in one column

    Try in C12 copied down

    =SUMIFS($D$2:$D$9,$A$2:$A$9,$A12,$B$2:$B$9,"*"&$B12&"*")
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    03-09-2020
    Location
    London, England
    MS-Off Ver
    2013
    Posts
    15

    Re: SUMIF with multiple criteria in one column

    Thanks Jeff!

    I can see that it works in the spreadsheet you attached but for some reason when I copy and paste it, it doesn't work and returns a value of zero?

    It'd also be great if you could explain how the formula works as I'll have to replicate it for more countries and with more than two criteria in the same column.
    Attached Files Attached Files

  9. #9
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,055

    Re: SUMIF with multiple criteria in one column

    As I mentioned in post #2, take a look at A12 and A13. For some reason, you have an extra space at the end. In this case, that makes A12 and A13 not match anything in A2:A9.

    To test this, place in A15 >> = A12=A2

    You will get FALSE. Remove the space and the end of A12 and then you should get TRUE. We could use a trim to counteract this, but it's just as easy to manually remove the spaces.Now the formula. Basically you are looking for anything that has business services in it's name.

    "*"&$B12&"*"

    This says, match everything where we don't care what the beginning or end is, but we want to match anywhere where B2:B9 matches "business services"

    Does this help?

  10. #10
    Registered User
    Join Date
    03-09-2020
    Location
    London, England
    MS-Off Ver
    2013
    Posts
    15

    Re: SUMIF with multiple criteria in one column

    Ah I see, that's v helpful.

    So, my fault for not clarifying but on the real workbook there's 4 products that include "business services" in the name but I only want 2 of those products. I've put the product names into the attached workbook.
    Attached Files Attached Files

  11. #11
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,055

    Re: SUMIF with multiple criteria in one column

    Are there only four categories?

    Other finance business services
    Technical services
    Other business services n.i.e.
    Employment services

    The reason I ask, the results below line up exactly with the results above number wise. There must be more data involved and this is a very very simplified version? True or not
    Last edited by jeffreybrown; 03-11-2020 at 11:50 AM.

  12. #12
    Registered User
    Join Date
    03-09-2020
    Location
    London, England
    MS-Off Ver
    2013
    Posts
    15

    Re: SUMIF with multiple criteria in one column

    Yes this is a very simplified version. The four products in the real workbook are:
    Services: Other business services
    Services: Technical, trade-related, and other business services
    Services: Other business services n.i.e.
    Services: Other business services n.i.e.; Of which: Employment services

    There are also 3872 rows of data: 143 different products for 27 countries.

    Essentially, I have raw data of the exports of lots of different products that I want to sum up into product groups for each country. E.g. Other business services and Other business services n.i.e. = Other business services, Cereals, vegetables, rice and sugar cane = agriculture; textiles, wearing apparel and, leather an related products = textiles and clothing.

  13. #13
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,055

    Re: SUMIF with multiple criteria in one column

    As Richard pointed out in post #5, can you not put all of this into a pivot table?

  14. #14
    Registered User
    Join Date
    03-09-2020
    Location
    London, England
    MS-Off Ver
    2013
    Posts
    15

    Re: SUMIF with multiple criteria in one column

    So ideally, I'd like formulas to do this instead of a pivot table. The longer term plan is to make a database with complex formulas so we can do more with the data beyond product groupings.

    But for now I could make a pivot table yes. As there's so much data however I would like to make a pivot table in the format of the table in the attached already as that is the format of the data once downloaded if that's possible?

    Also, sorry I know I'm being very slow but is there also a way to save filters into automatic product groupings so it's easier for people to select a product group? i.e. instead of having to select the different products (textiles, wearing apparel and, leather an related products) to get a product group, they can just select "textiles and clothing" ?
    Attached Files Attached Files

  15. #15
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,055

    Re: SUMIF with multiple criteria in one column

    Quote Originally Posted by Workaccount1998 View Post
    Also, sorry I know I'm being very slow but is there also a way to save filters into automatic product groupings so it's easier for people to select a product group? i.e. instead of having to select the different products (textiles, wearing apparel and, leather an related products) to get a product group, they can just select "textiles and clothing" ?
    That is why a pivot table would work great. You can slice and dice all sorts of combinations.

    But for now I could make a pivot table yes. As there's so much data however I would like to make a pivot table in the format of the table in the attached already as that is the format of the data once downloaded if that's possible?
    Yes it's possible, but you have just provided the table, but not the data. Can I assume the 10613 for AT and the EU comes from multiple records? Maybe so, maybe not. But without seeing the data it's hard for me to see the big picture.

  16. #16
    Registered User
    Join Date
    03-09-2020
    Location
    London, England
    MS-Off Ver
    2013
    Posts
    15

    Re: SUMIF with multiple criteria in one column

    Okay thanks v much for your help btw.

    I've attached a workbook which shows how the data is laid out - this goes on for 143 ITEMS for 27 countries.
    Attached Files Attached Files

  17. #17
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,055

    Re: SUMIF with multiple criteria in one column

    With this last attachment, what I see are 89 rows which can be boiled down to 79 rows as there are 10 dups.

    You are most likely trying to skinny this list down, but before you create groupings, it's hard to make suggestions.

    Recommendation is to create a helper column, column C and start grouping. Maybe out of the 89 rows, group them in tables and then return a value from the table. See if this example helps at all.

    On Sheet2 you can take all those categories in column A and group them to the table in column D:E.

    Then on Sheet1, there is a vlookup to the table to pull in the grouping which feeds the pivot table on Sheet3. This is at least the direction I would go.
    Attached Files Attached Files

  18. #18
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    27,184

    Re: SUMIF with multiple criteria in one column

    Personally, and if it's something you're doing regularly and can't change your underlying system to provide the data in a different format, I'd create a simple macro to put the data into the format I suggested much earlier, then use a PT.

    i.e.
    1. Insert a new column C which will hold the Location details, i.e. EU, UK, Extra EU
    2. Enter the Location in C2:Cxx
    3. Copy and paste A2:Bxx to the next available cell in column A
    4. 2. Enter the Location in Cxx+1:Cxxx
    5. Copy and paste E2:Exx to the next available cell in column D

    Repeat the above for the last location

  19. #19
    Registered User
    Join Date
    03-09-2020
    Location
    London, England
    MS-Off Ver
    2013
    Posts
    15

    Re: SUMIF with multiple criteria in one column

    Thanks Jeff. My project has now changed although the problem is similar. I'll try to explain as best as I can.

    In the "Sectors" sheet, I want to sum up sectors from the "Goods" sheet. Each sector has product groups associated with it e.g. Agriculture has product groups that start with 01, 02 and 03.

    I've used your formula however the issue is "01" appears in other products at the end or middle.

    Is there any way to nest a LEFT function into your SUMIFS formula so that the SUMIFS in the "Sectors" sheet only pick up products in the "Goods" sheet where 01 is the first two numbers?

    Attaching a workbook to demonstrate.
    Attached Files Attached Files

  20. #20
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,055

    Re: SUMIF with multiple criteria in one column

    Something like this?
    Attached Files Attached Files

  21. #21
    Registered User
    Join Date
    03-09-2020
    Location
    London, England
    MS-Off Ver
    2013
    Posts
    15

    Re: SUMIF with multiple criteria in one column

    Thank you so much, this has worked perfectly!

  22. #22
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,055

    Re: SUMIF with multiple criteria in one column

    Good to hear. You are very welcome and thanks for the feedback.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

  23. #23
    Registered User
    Join Date
    03-09-2020
    Location
    London, England
    MS-Off Ver
    2013
    Posts
    15

    Re: SUMIF with multiple criteria in one column

    Thanks Jeff, I've done that.

    My problem is solved however it would be great - and tell me if I'm asking too much - if we could also nest a HLOOKUP so that it automatically selects GB United Kingdom? As I would want to replicate this formula for EU INTRA and EU EXTRAa.

  24. #24
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,055

    Re: SUMIF with multiple criteria in one column

    I think this is what you are asking for. I changed C1 into a data validation drop down and the formulas in C read the chosen title and return those results.
    Attached Files Attached Files
    Last edited by jeffreybrown; 03-13-2020 at 12:04 PM.

  25. #25
    Registered User
    Join Date
    03-09-2020
    Location
    London, England
    MS-Off Ver
    2013
    Posts
    15

    Re: SUMIF with multiple criteria in one column

    That's brilliant thanks so much!! If I knew you in person, I'd buy you some chocolate.

    I do however have another, unrelated problem for the same dataset.

    Some of the data is replicated e.g. "303 Air and spacecraft and related machinery" and "3030 Air and spacecraft and related machinery" so I want to get rid of the one of them. However I can't simply get rid of the duplicate data the normal way because the data is replicated for 26 other countries so simply getting rid of "Air and spacecraft and related machinery" would get rid of the values for the other 26 countries and also, the cell is different because one has code "303" and one has code "3030" even though the data is the same.

    Bearing in mind that the original data has over 140 products with an unknown amount of duplications, is there any way to solve this? Perhaps using some sort of macro?

  26. #26
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,055

    Re: SUMIF with multiple criteria in one column

    Quote Originally Posted by Workaccount1998 View Post
    That's brilliant thanks so much!! If I knew you in person, I'd buy you some chocolate.
    And trust me, I'd accept.

    I believe this follow-up query is creeping outside of the original question. It's probably time to start a new thread.

    With that new post, make sure to attach a representative sample of your data with a clear before and after.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Sumif Index match with multiple column same criteria
    By lilym9053 in forum Excel General
    Replies: 3
    Last Post: 01-08-2016, 12:38 PM
  2. Column name for Active cell and Sumif UDF (multiple criteria)
    By mangesh.mehendale in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-29-2015, 12:06 AM
  3. [SOLVED] SUMIF with multiple column and row criteria
    By Zimbo in forum Excel Formulas & Functions
    Replies: 25
    Last Post: 03-12-2015, 12:54 AM
  4. [SOLVED] Multiple Criteria SUMIF Using Column as Negative Criteria
    By freybe06 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-24-2014, 04:46 PM
  5. SUMIF for multiple column/sheet criteria
    By preddy1110 in forum Excel General
    Replies: 1
    Last Post: 04-13-2012, 11:45 AM
  6. Multiple Sumif (Criteria within same column)
    By Thanksinadvance in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 07-20-2011, 09:35 AM
  7. SumIF multiple column criteria
    By gd19 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-05-2008, 05:56 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1