Skip to main content
Solved

IF Formula to compute a total based on the possibility of two fields

  • December 15, 2024
  • 4 replies
  • 35 views
C
Forum|alt.badge.img+4

Hello all!

I need help with a formula field. I believe I will need to use the IF function. Each week, I have to do math based on data input in 2 different fields and then multiply that number by a static field to get my total. I have to do this for 50+ records each week, so I'd love to save myself the time and energy and have my TOTAL field know which of my two fields it should compute against my static field. A screenshot is provided so you can see, and below is a little more explanation.

Thank you for any help!

Each week, some of the shows I am tracking are in preview performances and some are in actual performances, but never both at the same time. This number can change each week for either, and each show will start in previews then move to performances. I input the number of performances into the appropriate column, then multiple this by the {Production Seats} field to get my {Capacity}. 

Basically IF {Previews} has a number in it, multiply that by the {Production Seats} to get {Capacity}
or IF {Performances} has the number in it, to multiply that by {Production Seats} to get {Capacity}

Best answer by TheTimeSavingCo

Does this look right?

IF( Previews, Previews, Performances ) * {Production Seats (from Linked table)}

If both a 'Preview' and 'Performance' value exists then it'll default to using 'Preview'.  Let mw know if this is a problem and I'll see what I can do!

Link to base

1 Attachments

4 replies

ScottWorld
Forum|alt.badge.img+35
  • ScottWorld
  • Genius
  • December 16, 2024

Are you saying that you will NEVER have a record/row that has BOTH a number typed in for previews AND a number typed in for performances? It's either one or the other?

If so, then I would recommend a completely different setup that would make things much simpler for you and would also allow you to generate some advanced reporting from your numbers, such as pivot tables.

Instead of having 2 columns — one for previews and one for performances — I would recommend having just 1 column for "number of shows".

Then, create a single-select field for "type of show" with the options of "preview" and "performance".

That way, each one of your lines is tagged as either a preview or performance, and there is only one number field that you need to look at.

This opens the door to all sorts of advanced automations, advanced functionality, and advanced reporting down the line! And it makes it much easier to do simple things too, like filtering/grouping/sorting/coloring.

Hope this helps! If you’d like to hire an expert Airtable consultant to help you with anything Airtable-related, please feel free to contact me through my website: Airtable consultant — ScottWorld

TheTimeSavingCo
Forum|alt.badge.img+31

Does this look right?

IF( Previews, Previews, Performances ) * {Production Seats (from Linked table)}

If both a 'Preview' and 'Performance' value exists then it'll default to using 'Preview'.  Let mw know if this is a problem and I'll see what I can do!

Link to base

Adam, Airtable Consultant - thetimesaving.company
C
Forum|alt.badge.img+4
  • ckanayag
  • Author
  • New Participant
  • December 16, 2024

Are you saying that you will NEVER have a record/row that has BOTH a number typed in for previews AND a number typed in for performances? It's either one or the other?

If so, then I would recommend a completely different setup that would make things much simpler for you and would also allow you to generate some advanced reporting from your numbers, such as pivot tables.

Instead of having 2 columns — one for previews and one for performances — I would recommend having just 1 column for "number of shows".

Then, create a single-select field for "type of show" with the options of "preview" and "performance".

That way, each one of your lines is tagged as either a preview or performance, and there is only one number field that you need to look at.

This opens the door to all sorts of advanced automations, advanced functionality, and advanced reporting down the line! And it makes it much easier to do simple things too, like filtering/grouping/sorting/coloring.

Hope this helps! If you’d like to hire an expert Airtable consultant to help you with anything Airtable-related, please feel free to contact me through my website: Airtable consultant — ScottWorld


You make a great point, and I think this may be the way forward. I've been copying info from a sheet sent to me for these two columns, so keeping it consistent with the sheet I got sent to me was more straightforward. (since importing the data doesn't work with the linked fields, etc in Airtable vs the columns in the sheet with its mapping capabilities)
Thanks, Scott!

C
Forum|alt.badge.img+4
  • ckanayag
  • Author
  • New Participant
  • December 16, 2024

Does this look right?

IF( Previews, Previews, Performances ) * {Production Seats (from Linked table)}

If both a 'Preview' and 'Performance' value exists then it'll default to using 'Preview'.  Let mw know if this is a problem and I'll see what I can do!

Link to base


Hi Adam!
This is what I needed. I am not that new to Airtable, but I am a bit of a novice when I need minor scripting/formulas, and I was getting hung up on using two IF's in my attempts. This worked beautifully. Thank you for your time!

ckanayag

TheTimeSavingCo
Terms & ConditionsAccessibility statement