Assign values (numbers) for each component of Multiselect Field & calculate sum of each combination

[Asanka_Napagoda]

Hello,

I am looking for a method/formula to calculate the total time allocated for products (for manufacturing) which have different components with pre-defined times.

For instance, A product called “GS60” can be manufactured from one or mix of “RCBO+GPO”, “SA Meter”, “Modem” and so on. I have pre-defined time for each component such as “RCBO+GPO = 1h”, "SA Meter = 1:5h etc.

Once I select the components from multiselect field, it should be calculated the total time for each product in “Total Time” field.

Thanks

[Justin_Barrett · Accepted solution]

Welcome to the community, @Asanka_Napagoda! :grinning_face_with_big_eyes:

For each piece, you could use the FIND() function to determine if it exists in the {Components} field, then check to see if that number is greater than zero. For example:

FIND("RCBO+GPO", Components) > 0

That expression will return 1 or 0: 1 if the item is found, zero otherwise. Multiply that result by the number of hours that this component requires.

(FIND("RCBO+GPO", Components) > 0) * 1

That will return the appropriate time if the piece exists, or zero if not. Add all of these calculations together, and you get the total time for all selected pieces. Here’s a slightly larger example:

((FIND("RCBO+GPO", Components) > 0) * 1) +
((FIND("SA Meter", Components) > 0) * 1.5) +
((FIND("Modem", Components) > 0) * 0.75) +
...etc

I’m working on a course all about Airtable formulas, and it includes little logic tricks like this. I’m making good headway with it, and hope to have it out within the next month or two. Keep an eye on the forum for more details!

[Asanka_Napagoda]

@Justin_Barrett :grinning_face_with_big_eyes: Genius. That does the job. Thank you so much.