SUMIF with multiple conditions + Wildcard in Name

[DaleVacaro]

Hi All,

May you help me with below? Name in Column "H" will be the lookup value vs "A" and "D". Other condition is in the table:

 

[kweaver]

It would be considerably more helpful if you'd provide your sample data using XL2BB.

 

[DaleVacaro]

It would be considerably more helpful if you'd provide your sample data using XL2BB.

actually dont know how to attachh but let mee checkk, thank youu!

 

[Fluff]

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[kweaver]

Tested with only a few rows of data (6 w/data and 1 header row). Surely, there's another approach that would be shorter and possible more efficient, but I tried to implement the logic first before trying to simplify.

=SUMPRODUCT(($B$2:$B$7)*($E$2:$E$7="Apple")*($F$2:$F$7="Yes")*(1<=--LEFT($C$2:$C$7,1))*(7>=--LEFT($C$2:$C$7,1))*(IF(ISNUMBER(FIND(LEFT(H2,FIND("Country",H2)-2),$D$2:$D$7)),1,0)))

 

[DaleVacaro]

Tested with only a few rows of data (6 w/data and 1 header row). Surely, there's another approach that would be shorter and possible more efficient, but I tried to implement the logic first before trying to simplify.

=SUMPRODUCT(($B$2:$B$7)*($E$2:$E$7="Apple")*($F$2:$F$7="Yes")*(1<=--LEFT($C$2:$C$7,1))*(7>=--LEFT($C$2:$C$7,1))*(IF(ISNUMBER(FIND(LEFT(H2,FIND("Country",H2)-2),$D$2:$D$7)),1,0)))

Hello kweaver! Thank you very much it works! Can you help me incorporate Column A too? Needs to match this too with country name with other details I have

 

[Eric W]

I too tested on a subset of the data, which is why the totals below don't match what you'd expect from your test data. But if you put just the name in column H, instead of having to extract it, you can use this formula:

Book1
	H	I
1	Name	Formula
2	United States	20
3	New York	0
4	Malaysia	0
5	Thailand	0
Sheet3

Cell Formulas
Range	Formula
I2:I5	I2	=SUM(SUMIFS(B:B,A:A,"*"&H2&"*",C:C,{1,2,3,4,5,6,7}&"*",D:D,"*"&H2&"*",E:E,"Apple",F:F,"Yes"))

 

[joeyslaptop]

I'm the opposite of a fan of array formulas. You might consider using a helper column, if you're like me. There is also a "SUMIFS" formula that allows you to include only results that meet multiple criteria from multiple columns. That format is like this:

=SUMIFS(column to sum, criteria column 1, criteria, criteria column 2, criteria, ....).

 

[kweaver]

Hello kweaver! Thank you very much it works! Can you help me incorporate Column A too? Needs to match this too with country name with other details I have

=SUMPRODUCT(($B$2:$B$7)*($E$2:$E$7="Apple")*($F$2:$F$7="Yes")*(1<=--LEFT($C$2:$C$7,1))*(7>=--LEFT($C$2:$C$7,1))*(IF(ISNUMBER(FIND(LEFT(H4,FIND("Country",H4)-2),$D$2:$D$7)),1,0))*(IF(ISNUMBER(FIND(LEFT(H4,FIND("Country",H4)-2),$A$2:$A$7)),1,0)))