Logical Formula involving 5 cells

sanket_sk

Board Regular
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

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
try this

Book1
ABCDEFG
1Turn around time/Payment
2CityTypePurposeLocation<22-44-6
3Delhi , IndorePolishPreventiveField705030
4Delhi , IndorePolishRegularWorkshop50300
5Delhi , IndoreCleaningPreventiveHospital604030
6Delhi , IndoreFinishPreventiveHospital604030
7Delhi , IndoreCleaningRegularHospital504020
8Delhi , IndoreFinishRegularHospital504020
9OthersPolish20
10OthersCleaning302010
11OthersFinish302010
12
13
14
15CityTypePurposeLocationTurn around timePayment
16Delhi , IndoreFinishRegularHospital340
17LondonCleaning510
Sheet5
Cell Formulas
RangeFormula
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}))))

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

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

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

Code:
``=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}))))``

Replies
1
Views
190
Replies
0
Views
270
Replies
15
Views
400
Replies
5
Views
202
Replies
5
Views
298

1,203,468
Messages
6,055,599
Members
444,800
Latest member
KarenTheManager

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back