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

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

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

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

See attached

Try in C12 copied down

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

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?

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

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

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.

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.

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

Something like this?

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.

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.

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.