If cell contains certain texts, search using other texts

[abschy]

Hi all,

I currently have these 2 formulas that i require some help with:
1) =IFERROR(ROUNDUP(SUMIFS('Combined Data'!$M$5:$M$437,'Combined Data'!$A$5:$A$437,'Formula Testing'!$B$4,'Combined Data'!$C$5:$C$437,'Formula Testing'!C$7,'Combined Data'!$M$5:$M$437,">0")/COUNTIFS('Combined Data'!$A$5:$A$437,$B$4,'Combined Data'!$C$5:$C$437,'Formula Testing'!C$7,'Combined Data'!$M$5:$M$437,">0")*'Formula Testing'!C$8,0),"-")

2) =IFERROR(ROUNDUP(TRIMMEAN(IF('Combined Data'!$A$5:$A$437=$B$4,IF('Combined Data'!$C$5:$C$437=C$7,IF(ISNUMBER('Combined Data'!$M$5:$M$437),'Combined Data'!$M$5:$M$437))),0.1)*C$25,0),"")

Currently, what these formulas do is it calculates an average of data depending on what country is in cell B4.
Both formulas work well for me, but now the issue comes when i want to group countries together.
The countries i have to choose from in B4 are in a drop down list and are all individually listed.

Now, I want to group certain countries together. For example, countries like Thailand, Vietnam and Indonesia will be considered "SEA" for South East Asia.

How can i still work my formulas such that, if "SEA" is selected, my formula uses data from ALL those countries instead of just a specific country?

Thanks all!!

 

[sijpie]

I can't figure a neat way to do it with one formula. A workaround would be to have in three cells (on a different sheet for instance) these formula's worked out, one for Thailand, one for Indonesia and one for Vietnam. Say in sheet Formulas, in cells A1, A2 and A3

Then on the main sheet you have:
=IF('Formula Testing'!$B$4,"SEA",'Formulas'!A1+'Formulas'!A2+'Formulas'!A3,IFERROR(ROUNDUP(SUMIFS('Combined Data'!$M$5:$M$437,'Combined Data'!$A$5:$A$437,'Formula Testing'!$B$4,'Combined Data'!$C$5:$C$437,'Formula Testing'!C$7,'Combined Data'!$M$5:$M$437,">0")/COUNTIFS('Combined Data'!$A$5:$A$437,$B$4,'Combined Data'!$C$5:$C$437,'Formula Testing'!C$7,'Combined Data'!$M$5:$M$437,">0")*'Formula Testing'!C$8,0),"-") )

 

[Eric W]

Here's one option:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1										Code	Countries
2										Thailand	Thailand
3		Code		Matches	Formula 1	Formula 2				Vietnam	Vietnam
4		SEA		Thailand	6	11				Laos	Laos
5				Vietnam						Myanmar	Myanmar
6				Indonesia						Indonesia	Indonesia
7			a							China	China
8			1.5							SEA	Thailand	Vietnam	Indonesia
9										Asia	Thailand	Vietnam	Indonesia	Laos	Myanmar	China
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25			2.7
Formula Testing

Cell Formulas
Range	Formula
E4	E4	=IFERROR(ROUNDUP(SUMPRODUCT('Combined Data'!$M$5:$M$437,--('Combined Data'!$A$5:$A$437<>""),--ISNUMBER(MATCH('Combined Data'!$A$5:$A$437,'Formula Testing'!$D$4:$D$13,0)),--('Combined Data'!$C$5:$C$437='Formula Testing'!C$7),--('Combined Data'!$M$5:$M$437>0))/SUMPRODUCT(--('Combined Data'!$A$5:$A$437<>""),--(ISNUMBER(MATCH('Combined Data'!$A$5:$A$437,'Formula Testing'!$D$4:$D$13,0))),--('Combined Data'!$C$5:$C$437='Formula Testing'!C$7),--('Combined Data'!$M$5:$M$437>0))*'Formula Testing'!C$8,0),"-")
F4	F4	=IFERROR(ROUNDUP(TRIMMEAN(IF(ISNUMBER(MATCH('Combined Data'!$A$5:$A$437,$D$4:$D$13,0)),IF('Combined Data'!$C$5:$C$437=C$7,IF(ISNUMBER('Combined Data'!$M$5:$M$437),'Combined Data'!$M$5:$M$437))),0.1)*C$25,0),"")
D4:D13	D4	=TRANSPOSE(INDEX(K2:T20,MATCH(B4,J2:J20,0),0))&""
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

Cells with Data Validation
Cell	Allow	Criteria
B4	List	=OFFSET(J2,0,0,COUNTA(J2:J100))

Create a table with your countries, as well as the country groups. I show it here in J1:P9, but you'd probably want it on another sheet. Each country, such as Thailand, has its own entry with one value in the list. Then you can create groups with multiple countries, such as SEA and Asia.

Next change your data validation formula to check the code from column J. Now create a new formula in column D. It basically grabs the code from B4, finds the matching row, and returns the list in D4:D13.

Finally, change your formulas to look at the list in D4:D13 instead of just the B4 value. I found it easier to use SUMPRODUCT instead of SUMIFS, but SUMIFS can work too.

Hope this helps!