Adding IF formula to a column with an existing concatenate formula

[Contact_Lights_]

Hey guys!

I’m looking to add an if formula to the formula below. What I need is that if the “fixture model #”, the “bulb model #” or the “miscellaneous” fields are empty, the output of the concatenate funtion is “N/A”

CONCATENATE({Name (from Orders)}," - “,”(",Quantity,") “,{Fixture Model #},” / “,{Bulb Model #},” / ",{Miscellaneous})

Thanks in advance!

[Justin_Barrett]

Welcome to the community, @Emma_Garwood! :grinning_face_with_big_eyes: As @kuovonne suggested above, I recommend dropping CONCATENATE() and joining the items with the & operator instead. Here’s the revision with that change applied, and each group only appearing if the relevant fields are filled:

{1st Quantity} & " x " & {1st Pair} &
IF(AND({2nd Quantity}, {2nd Pair}), "\n" & {2nd Quantity} & " x " & {2nd Pair}) & 
IF(AND({3rd Quantity}, {3rd Pair}), "\n" & {3rd Quantity} & " x " & {3rd Pair}) & 
IF(AND({4th Quantity}, {4th Pair}), "\n" & {4th Quantity} & " x " & {4th Pair})

[Emma_Garwood]

Thanks so much Kuovonne - it’s going to take me some time to get my head round it, but really appreciate the fix for now :slightly_smiling_face:

[Emma_Garwood]

Justin_Barrett:

{1st Quantity} & " x " & {1st Pair} &
IF(AND({2nd Quantity}, {2nd Pair}), "\n" & {2nd Quantity} & " x " & {2nd Pair}) & 
IF(AND({3rd Quantity}, {3rd Pair}), "\n" & {3rd Quantity} & " x " & {3rd Pair}) & 
IF(AND({4th Quantity}, {4th Pair}), "\n" & {4th Quantity} & " x " & {4th Pair})

that’s so great, thanks Justin! I don’t know how you guys work this out - i’ve got a lot of learning to do! works a dream :slightly_smiling_face:

[Justin_Barrett]

Glad to know that you got the answer you were seeking! When it comes to working this stuff out, I’ll repeat something I said in another thread recently: a lot of it has to do with patterns. Learn to see the patterns in both the data you’re analyzing, and the scripting options available, and you can learn how to systematically process the data.

I’ve got a YouTube channel that’s all about Airtable, and my initial plan for it was to do a lot of deep diving into how certain parts of Airtable work. It’s taking longer than I thought to make that happen, though. While I still feel that’s useful, I felt prompted recently to shift gears slightly and start breaking down the logic behind various formula solutions and base designs I’ve shared here. It’ll be a lot more informal, but I get the feeling that it could be just as beneficial as some of the deeper topics I’ve got planned. Anyway, this formula will be in the first video of that new series, and I’ll be sure to let you know when it’s live.

[Emma_Garwood]

That sounds like it’s going to be a great watch. I learn well by watching videos - unless it’s how to make a soufflé rise… you haven’t got a formula for that, have you?!

[Justin_Barrett]

Haha…nope, I’m not the one to go to for cooking help. I like a lot of oddball things, and often just make it up as I go depending on what I’m craving at the moment. That, and I’ve never made a soufflé. :slightly_smiling_face: