Formulas to Exclude Items from one table based on criteria from a different table

[gberg]

I have a spreadsheet with two tables

Table 1 holds the main data and could be thousands of lines long

• This is the table where the bulk data is held
• Most data will have an "Area" assigned to it
• There will be a few items that the area = "(unassigned)"

Table 2 will Distribute the "(unassigned)" items proportionally to the desired areas

• Areas designated with "Exclude" will not have the values distributed

TABLE 1

	A​	B​	C​
1​	Area	Section	Value
2​	(unassigned)	Widget 1	1000​
3​	(unassigned)	Widget 2	2000​
4​	(unassigned)	Widget 3	3000​
5​	North	Section 1	100​
6​	South	Section 2	200​
7​	East	Section 3	300​
8​	West	Section 4	400​
9​	North	Section 5	500​
10​	South	Section 1	1000​
11​	East	Section 2	2000​
12​	North	Section 3	3000​
13​	North	Section 4	4000​
14​	West	Section 5	5000​
15​	West	Section 1	10000​
16​	East	Section 2	11000​
17​	South	Section 3	12000​
18​	East	Section 4	13000​
19​	North	Section 5	14000​

TABLE 2

	A​	B​	C​	D​
1	Items	Area	Distribute	Value
2	Widget 1	North		583.78
3	Widget 1	South	Exclude
4	Widget 1	East	Exclude
5	Widget 1	West		416.22
6
7	Widget 2	North	Exclude
8	Widget 2	South		480.87
9	Widget 2	East		958.11
10	Widget 2	West		561.02
11
12	Widget 3	North	Exclude
13	Widget 3	South	Exclude
14	Widget 3	East		3,000.00
15	Widget 3	West	Exclude

So in this example for Widget 1 I want to distribute the total value (1,000) to Areas "North" and "West" (note: these areas are just examples, there could be 2 areas or 100 areas)
The formula I am looking for is for column D to come up with the appropriate distributed values based on areas that are not designated as "Excluded" in column C

• So the formula for D2 would be (North Value (21,600) / ((North (21,600)+West(15,400))))*Widget 1 value (1,000) = 583.78

I just don't know how to make a formula work to automatically calculate this

 

[jtakw]

Hi,

The way you have your TABLE 2 set up is not exactly formula friendly...
Also not sure if your 2 Tables are on the same sheet or not, so Add Sheet Name, Change/Adjust Cell References/Ranges as needed:

Book3.xlsx
	A	B	C	D	E	F	G	H	I
1	Area	Section	Value			Items	Area	Distribute	Value
2	(unassigned)	Widget 1	1000			Widget 1	North		583.78
3	(unassigned)	Widget 2	2000			Widget 1	South	Exclude
4	(unassigned)	Widget 3	3000			Widget 1	East	Exclude
5	North	Section 1	100			Widget 1	West		416.22
6	South	Section 2	200
7	East	Section 3	300			Widget 2	North	Exclude
8	West	Section 4	400			Widget 2	South		480.87
9	North	Section 5	500			Widget 2	East		958.11
10	South	Section 1	1000			Widget 2	West		561.02
11	East	Section 2	2000
12	North	Section 3	3000			Widget 3	North	Exclude
13	North	Section 4	4000			Widget 3	South	Exclude
14	West	Section 5	5000			Widget 3	East		3000.00
15	West	Section 1	10000			Widget 3	West	Exclude
16	East	Section 2	11000
17	South	Section 3	12000
18	East	Section 4	13000
19	North	Section 5	14000
Sheet915

Cell Formulas
Range	Formula
I2:I15	I2	=IF(OR(F2="",H2<>""),"",SUMIFS(C$2:C$19,A$2:A$19,G2)/SUM(SUMIFS(C$2:C$19,A$2:A$19,IF((F$2:F$15=F2)*(H$2:H$15=""),G$2:G$15)))*SUMIFS(C$2:C$19,A$2:A$19,"(unassigned)",B$2:B$19,F2))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[gberg]

That's awesome, worked perfectly! Thanks so much for your help

 

[jtakw]

You're welcome, thanks for the feedback.

Don't forget to mark the post that solved your question as solution. Thanks.

 

[gberg]

Sorry for the delay, just marked as "Solved"