Sum based on data validation cell

[davidrendle999]

Hi All,
I haven't been able to find a similar query to this that has been answered, apologies if that's the case. I am looking for a way to display a difference between two cells based on a drop down data validation selection. I hope I can explain this properly.

Essentially I have two tables of information. Like this:
Table 1

A	12
B	34
C	52
D	11
E	43
F	23

Table 2

A	200
B	200
C	300
D	200
E	300
F	300

What I would like to achieve is a cell that will display the answer of the 'Table 2's A score e.g 200 minus 'Table 1's A Score e.g 12 based on a cell next to it with a drop down list of A,B,C,D,E,F. So if I selected F I would get '300-23' or E '300-43' etc.

I hope I have explained adequately what I mean here, thank you so much in advance for any support.

 

[etaf]

something like a sumif()-sumif()
OR maybe a lookup

What cell is the dropdown in -

Lets assume a different table 3

=SUMIF(Table2 criteria range , dropdown value, sumrange ) - SUMIF(Table1 criteria range , dropdown value, sumrange )

Book1
	A	B	C	D	E	F	G	H
1	Table 1			Table 2			Dropdown	A
2	A	12		A	200
3	B	34		B	200		Total	188
4	C	52		C	300
5	D	11		D	200
6	E	43		E	300
7	F	23		F	300
Sheet2

Cell Formulas
Range	Formula
H3	H3	=SUMIF(D2:D7,H1,E2:E7)-SUMIF(A2:A7,H1,B2:B7)

 

[davidrendle999]

Thank you for the response!

I'm sorry, I'm not quite sure I follow.

The drop down list will be in Cell A1. Cell A2 would be the cell to show the results of table 2 - table 1 based on Call A1 being A,B,C,D,E or F. I hope that clarifies.

Thank you!

 

[etaf]

where are the tables ?

Book1
	A	B	C	D	E
1	A
2	188
3
4	Table 1			Table 2
5	A	12		A	200
6	B	34		B	200
7	C	52		C	300
8	D	11		D	200
9	E	43		E	300
10	F	23		F	300
Sheet2

Cell Formulas
Range	Formula
A2	A2	=SUMIF(D5:D10,A1,E5:E10)-SUMIF(A5:A10,A1,B5:B10)

 

[davidrendle999]

Table 1 is D1:E7 and Table 2 G1:H7

 

[etaf]

ok,
here is the excel again given your info, although the formula is the same

Book1
	A	B	C	D	E	F	G	H
1	A			Table 1			Table 2
2	188			A	12		A	200
3				B	34		B	200
4				C	52		C	300
5				D	11		D	200
6				E	43		E	300
7				F	23		F	300
8
9	Table 1 is D1:E7 and Table 2 G1:H7
Sheet2

Cell Formulas
Range	Formula
A2	A2	=SUMIF(G2:G7,A1,H2:H7)-SUMIF(D2:D7,A1,E2:E7)

added a lookup version
]

Book1
	A	B	C	D	E	F	G	H
1	A			Table 1			Table 2
2	188	using SUM		A	12		A	200
3				B	34		B	200
4				C	52		C	300
5	188	using lookup		D	11		D	200
6				E	43		E	300
7				F	23		F	300
8
9	Table 1 is D1:E7 and Table 2 G1:H7
Sheet2

Cell Formulas
Range	Formula
A2	A2	=SUMIF(G2:G7,A1,H2:H7)-SUMIF(D2:D7,A1,E2:E7)
A5	A5	=INDEX(H2:H7,MATCH(A1,D2:D7,0))-INDEX(E2:E7,MATCH(A1,D2:D7,0))

 

[davidrendle999]

This works absolutely perfectly, thank you so much!

 

[etaf]

you are welcome, added a lookup version to my last post