Calculated fields : selecting multiple checkboxes to get a total value

[AWoody]

I have a custom form with Field 1 as a dropdown and Field 2 as a single line text to input the quantity required (of Field 1). This formula works:
{DE:Field1}*{DE:Field2} and gives me a total. Happy.

Now I would like to have Field 1 as a multi checkbox with 5 options and each option has a value associated to it. Depending on which option/s are selected in Field1, a single line text field will then appear to input a quantity for each of the 5 options selected.
I want to update my calculated field to now give me a total value of Field 1 x Field 2 PLUS any of the other options.

I found this as a test, but it gives me invalid expression.

IF(CONTAINS("Field1Option1", (DE:Field1}), {DE:Field2}*value1,0)+
IF(CONTAINS("Field1Option2", (DE:Field1}), {DE:Field2}*value2,0)

And this one gives me 0:
IF(CONTAINS("Field1Option1", (DE:Field1})*{DE:Field2},0)+
IF(CONTAINS("Field1Option2", (DE:Field1})*{DE:Field2},0)

Please can someone help? I get so far and then I am stuck. Thank you!

[skyehansen]

Would you mind first correcting typos? I see your DE:Field1 has mismatched brackets on either side of it. Can you correct it to have curly braces on both sides, and then let us know what error messages you're getting? Thanks.

[AWoody]

There is now a no invalid expression with this format, but I do get 0 in the calculated field:

IF(CONTAINS("Field1Option1", {DE:Field1}), {DE:Field2}*value1,0)+
IF(CONTAINS("Field1Option2", {DE:Field1}), {DE:Field2}*value2,0)

There is no invalid expression with this format, but I do get N/A in the calculated field:

IF(CONTAINS("Field1Option1", {Field1})*{Field2}, 0)+

IF(CONTAINS("Field1Option2", {Field1})*{Field2}, 0)

[AWoody]

There is now a no invalid expression with this format, but I do get 0 in the calculated field:

IF(CONTAINS("Field1Option1", {DE:Field1}), {DE:Field2}*value1,0)+
IF(CONTAINS("Field1Option2", {DE:Field1}), {DE:Field2}*value2,0)

Sorry, corrected this one

There is no invalid expression with this format, but I do get N/A in the calculated field:

IF(CONTAINS("Field1Option1", {DE:Field1})*{DE:Field2}, 0)+

IF(CONTAINS("Field1Option2", {DE:Field1})*{DE:Field2}, 0)

[skyehansen]

great, well, that's some progress.

Can you take a look at this page, and use the SUM function from it?

https://experienceleague.adobe.com/en/docs/workfront/using/reporting/reports/calculated-custom-data/...

Start by trying to get one IF statement to work, e.g. see if you can get this to work: IF(CONTAINS("Field1Option1", {DE:Field1}), {DE:Field2}*value1,0)

If that IF statement works, then add it to your SUM in the "number1" spot, and for now, also add it as the number2 spot. 

IF the SUM function works (if your number doubles), then you can start creating all your variations (so edit the number2 spot to display the second option. And so on.

[_Manish_Singh]

Hello @AWoody - Your first calculated expression is almost correct. You just need to replace 'Field1Option' in the expression with the 'value' associated to the dropdown option. Here is an example:

IF(CONTAINS(value1,{DE:Field1}),{DE:Field2}*value1,0)+
IF(CONTAINS(value2,{DE:Field1}),{DE:Field2}*value2,0)+
IF(CONTAINS(value3,{DE:Field1}),{DE:Field2}*value3,0)

If the values are 1, 2 and 3, then it would be:

IF(CONTAINS(1,{DE:Field1}),{DE:Field2}*1,0)+
IF(CONTAINS(2,{DE:Field1}),{DE:Field2}*2,0)+
IF(CONTAINS(3,{DE:Field1}),{DE:Field2}*3,0)

[kautuk_sahni]

@AWoody just checking in! Were you able to get this resolved? If one of the replies above helped—whether it completely solved the issue or simply pointed you in the right direction—marking it as accepted can make it much easier for others with the same question to find a solution. And if you found a different way to fix it, sharing your approach would be a great contribution to the community. Your follow-up not only helps close the loop but also ensures others benefit from your experience. Thanks so much for being part of the conversation!