Find the Value

[Narend]

Respected genius I am attaching two files from which I want

Ans:- 1. question I have to find the value from Departmentwise+Quarterwise+Direction Wise i.e if we are changing the department or quarter the value of cell change automatically.

Ans:- 2nd question I have to Find the Sum as per Direction+Name+Year i.e. if we are changing the year the data will be change by direction wise and by name wise.

Tnanks

Narendra

 

[bosco_yip]

Question 1 (find value),

Find value from Departmentwise+Quarterwise+Direction J7, formula copy across and down:

=INDEX(($B3:$E6,$B10:$E13,$B17:$E20,$B24:$E27),MATCH($I7,{"Q1";"Q2";"Q3";"Q4"},0),MATCH(J$6,$B2:$E2,0),MATCH($I6,{"HR","OPS","Sale","Logistic"},0))

Regards
Bosco

 

[bosco_yip]

Question 2 (Sum as per Direction+Name+Year )

1] Sum of Sales K6, formula copy across and down :

=SUMIFS($D:$D,$C:$C,$J6,$E:$E,K$5,$F:$F,$J$5)

2] Sum of Bonus K6, formula copy across and down :

=SUMIFS($G:$G,$C:$C,$J13,$E:$E,K$12,$F:$F,$J$12)

Regards
Bosco

 

[Villalobos]

Hi Narend,

Q2: if you want to sum the Sales and Bonus (based on the criterias) in 1 formula then check this approach.