# SUMIF with multiple criteria in one column

1. ## 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!

2. ## 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

3. ## 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

4. ## 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 ...

5. ## 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

6. ## 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. ## 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&"*")

8. ## 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.

9. ## 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. ## 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.

11. ## Re: SUMIF with multiple criteria in one column

Are there only four categories?

Technical services
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

12. ## 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 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. ## 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. ## 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" ?

15. ## Re: SUMIF with multiple criteria in one column

Originally Posted by Workaccount1998
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. ## 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.

17. ## 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.

18. ## 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. ## 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.

20. ## Re: SUMIF with multiple criteria in one column

Something like this?

21. ## Re: SUMIF with multiple criteria in one column

Thank you so much, this has worked perfectly!

22. ## Re: SUMIF with multiple criteria in one column

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

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

25. ## 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. ## Re: SUMIF with multiple criteria in one column

Originally Posted by Workaccount1998
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.

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