Sumup and lookup(5 way lookup)

[aarky]

if I select any thing in employee column and "All" in department column I should get the correct figure in sales column

if I select ravi in employee column and "MIS" in department column I should get the correct figure in sales column

for example if I select Arjun in employee column and in department "All" I should get the correct sales figure irrespective of their departments.

its like 4/5 way lookup

 

[Taul]

Hi,
Give this a try

Forum Sales.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	Employee	Department	Sales							names	dept
2	Ravi	MIS	57820							All	All
3	Mahesh	Accounts	45633							Ravi	MIS
4	Arjun	Marketing	89000							Mahesh	Accounts
5	Ravi	MIS	57820			Employee	Department	Sales		Arjun	Marketing
6	Mahesh	Accounts	45633			All	All	555639		Kriti
7	Arjun	Marketing	77000							Neha
8	Kriti	MIS	52300							Vikram
9	Neha	Accounts	36900
10	Arjun	Accounts	45633
11	Vikram	Marketing	47900
12
Sheet1

Cell Formulas
Range	Formula
H6	H6	=IF(AND(F6="All",G6="All"),SUM(C2:C11),IF(F6="All",SUMIF(B2:B11,G6,C2:C11),IF(G6="All",SUMIF(A2:A11,F6,C2:C11),SUMPRODUCT($C$2:$C$11,($A$2:$A$11=$F6)*($B$2:$B$11=G$6)))))