Logical Formula involving 5 cells

[sanket_sk]

Dear All,

I am working on logical calculation where logic will involve 5 cells, can anybody help me build logic using below statement / Table

1> if City is Delhi , Indore -If type of work is "Polish" Purpose is "Preventive" Location of the work is "Field" Payment should be on Turn around time -- < 2- 70 , >2<4- 50, >4<6-30, >6=0

2> if City is Delhi , Indore- If type of work is "Polish" Purpose is "Regular" Location of the work is "Workshop" Payment should be on Turn around time -- < 2- 50 , >2<4- 30, >46=0

3> if City is Delhi , Indore - If type of work is "Cleaning or Finish" Purpose is "Preventive" Location of the work is "Hospital" Payment should be on Turn around time -- < 2- 60 , >2<4- 40, >4<6-30, >6=0

4> if City is Delhi , Indore- If type of work is "Cleaning or Finish" Purpose is "Regular" Location of the work is "Hospital" Payment should be on Turn around time -- < 2- 50 , >2<4- 40, >4<6-20, >6=0

5> if City is other than Delhi , Indore -If type of work is "Polish" Payment should be on Turn around time -- < 2- 20 , >2<3- 0, >4=0

6> if City is other than Delhi , Indore - If type of work is "Cleaning or Finish" Payment should be on Turn around time -- < 2- 30 , >2<4- 20, >4<6-10, >6=0

Sanket

 

[AlanY]

try this

Book1
	A	B	C	D	E	F	G
1					Turn around time/Payment
2	City	Type	Purpose	Location	<2	2-4	4-6
3	Delhi , Indore	Polish	Preventive	Field	70	50	30
4	Delhi , Indore	Polish	Regular	Workshop	50	30	0
5	Delhi , Indore	Cleaning	Preventive	Hospital	60	40	30
6	Delhi , Indore	Finish	Preventive	Hospital	60	40	30
7	Delhi , Indore	Cleaning	Regular	Hospital	50	40	20
8	Delhi , Indore	Finish	Regular	Hospital	50	40	20
9	Others	Polish			20
10	Others	Cleaning			30	20	10
11	Others	Finish			30	20	10
12
13
14
15	City	Type	Purpose	Location	Turn around time	Payment
16	Delhi , Indore	Finish	Regular	Hospital	3	40
17	London	Cleaning			5	10
Sheet5

Cell Formulas
Range	Formula
F16	=IF(A16="Delhi , Indore",SUMPRODUCT(--($B$3:$B$8=B16)*($C$3:$C$8=C16)*($D$3:$D$8=D16),INDEX($E$3:$I$8,,MATCH(E16,{0,2,4,6}))),SUMPRODUCT(--($B$9:$B$11=B16),INDEX($E$9:$I$11,,MATCH(E16,{0,2,4,6}))))

 

[sanket_sk]

Dear Alan,

Thanks for quick response but we have limitation of combining cities as you mentioned
Data is going to be more than 60k lines, secondly we have more than 60 cities which we need to map with different payment structure which cannot be combined in separate cell, please suggest formula without adding any information in existing sheet, we are OK with VBA as weel.

Thanks & Regards,
Sanket

 

[AlanY]

Dear Alan,

Thanks for quick response but we have limitation of combining cities as you mentioned
Data is going to be more than 60k lines, secondly we have more than 60 cities which we need to map with different payment structure which cannot be combined in separate cell, please suggest formula without adding any information in existing sheet, we are OK with VBA as weel.

Thanks & Regards,
Sanket

well, the suggestion in post#2 is based on the info on post#1.
if you have more cites payment structure you just have to extend the table at the top

 

[AlanY]

what you needed is to include the check of city, like this

=IF(A16="Delhi , Indore",SUMPRODUCT(--[COLOR="#FF0000"]($A$3:$A$8=A16)[/COLOR]*($B$3:$B$8=B16)*($C$3:$C$8=C16)*($D$3:$D$8=D16),INDEX($E$3:$I$8,,MATCH(E16,{0,2,4,6}))),SUMPRODUCT(--($B$9:$B$11=B16),INDEX($E$9:$I$11,,MATCH(E16,{0,2,4,6}))))