Hello,
I have a table shown in the attached excel file.
Now my requirement in that I want to append a new column to this table that counts a particular response('Quoted') in certain predefined columns for each line in the table. For the 'Space Type' 'Onsite', I want to count the number of columns from the below list of 19 columns that have the response 'Quoted' in them:
Minimum Proposed Years of Experience, Breakdown1, Breakdown2,Breakdown3,Breakdown4,Breakdown5,Breakdown6,Breakdown7,Weekday Standard (Turnaround / Shutdown) Hourly Rate,Weekday Overtime Multiplier,Weekday Standard Daily Rate,Weekend Multiplier,Weekend Overtime Multiplier,Weekend Daily Rate,Public Holiday Multiplier,Public Holiday Daily Rate,Monthly Rate,
Mobilisation / Demobilisation Overland Transport Rate Per KM For miles over 170KM,Per Diem For Travel over 170KM
For example, for line A1, all these fields are recorded as 'Quoted' so the column will have the value '19' in it.
Similarly, when 'Space Type' is 'Offsite', the count needs to be taken from the list of 15 columns below:
Minimum Proposed Years of Experience, Breakdown1, Breakdown2,Breakdown3,Breakdown4,Breakdown5,Breakdown6,Breakdown7,Weekday Standard (Turnaround / Shutdown) Hourly Rate,Weekday Overtime Multiplier,Weekday Standard Daily Rate,Standby Hourly Rate,Monthly Rate,
Mobilisation / Demobilisation Overland Transport Rate Per KM For miles over 170KM,Per Diem For Travel over 170KM
For example for line A351, all the columns in the list above are recorded as 'Quoted' so the column will have the number '15'.
I prefer this expression to be written in my script so that I can use it in further formulae in the front end. I have tried writing the formula as:
if([Space Type]='Onsite', RangeSum(Countif([Minimum Proposed Years of Experience]='Quoted'),Countif(Breakdown1='Quoted'),Countif(Breakdown2='Quoted'),Countif(Breakdown3='Quoted'),Countif(Breakdown4='Quoted'),Countif(Breakdown5='Quoted'),Countif(Breakdown6='Quoted'),Countif(Breakdown7='Quoted'),Countif([Weekday Standard (Turnaround / Shutdown) Hourly Rate]='Quoted'),Countif([Weekday Overtime Multiplier]='Quoted'),Countif([Weekday Standard Daily Rate]='Quoted'),Countif([Weekend Multiplier]='Quoted'),Countif([Weekend Overtime Multiplier]='Quoted'),Countif([Weekend Daily Rate]='Quoted'),Countif([Public Holiday Multiplier]='Quoted'),Countif([Public Holiday Daily Rate]='Quoted'),Countif([Monthly Rate]='Quoted'),Countif([Mobilisation / Demobilisation Overland Transport Rate Per KM For miles over 170KM]='Quoted'),Countif([Per Diem For Travel over 170K]='Quoted')),
if([Space Type]='Offsite',RangeSum(Countif([Minimum Proposed Years of Experience]='Quoted'),Countif(Breakdown1='Quoted'),Countif(Breakdown2='Quoted'),Countif(Breakdown3='Quoted'),Countif(Breakdown4='Quoted'),Countif(Breakdown5='Quoted'),Countif(Breakdown6='Quoted'),Countif(Breakdown7='Quoted'),Countif([Weekday Standard (Turnaround / Shutdown) Hourly Rate]='Quoted'),Countif([Weekday Overtime Multiplier]='Quoted'),Countif([Weekday Standard Daily Rate]='Quoted'),Countif([Standby Hourly Rate]='Quoted'),Countif([Monthly Rate]='Quoted'),Countif([Mobilisation / Demobilisation Overland Transport Rate Per KM For miles over 170KM]='Quoted'),Countif([Per Diem For Travel over 170K]='Quoted')))) as Count1
But it doesnt seem to work. Please help me define the expression for this column. Thanks in advance!
stalwar1 kaushik.solanki tresesco
