Help

Re: Formula? Summing Field Values Based on Checkbox True / False

975 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Josh_Vaughn
4 - Data Explorer
4 - Data Explorer

Hi,

First: Airtable is an incredibly useful program, both for business and personal use. It’s a lifesaver that I’ve been using for years.

However … I have need for Formulas for the first time and having trouble getting my head around the syntax for this task.

I need to conditionally add and output the sum of 3 Currency fields based on the status of the Checkboxes:

IF the status of {$VALUE 1} is checked - output of the Formula field is $VALUE 01. Otherwise the output of the Formula field is 0.

IF the status of {$VALUE 1} and {$VALUE 2} are checked - output of the Formula field is ($VALUE 01 + $VALUE 2). Otherwise the output of the Formula field remains $VALUE 1.

IF the status of {$VALUE 1} and {$VALUE 2} and {$VALUE 3} are checked - output of the Formula field is ($VALUE 01 + $VALUE 2 + $VALUE 3). Otherwise the output of the Formula field remains $VALUE 1 + $VALUE 2).

For this task the $VALUES will either be hit in sequence, or they won’t. ie: We will never have a case where we need to add and output $VALUE 01 + $VALUE 03 for instance.

I have some of the pieces of the puzzle but not nearly qualified to put them together yet. Any wizards out there feel like taking a crack at this or throwing me some clues? :laughing: Mucho gracias :pray:t2:

3 Replies 3

Hey man, try:

IF(
  AND(
    {Value 1 Checkbox} = 1,
    {Value 2 Checkbox} = 1,
    {Value 3 Checkbox} = 1
  ),
  {Value 1} + {Value 2} + {Value 3},
  IF(
    AND(
      {Value 1 Checkbox} = 1,
      {Value 2 Checkbox} = 1,
      {Value 3 Checkbox} = 0
    ),
    {Value 1} + {Value 2},
    IF(
      AND(
        {Value 1 Checkbox} = 1,
        {Value 2 Checkbox} = 0,
        {Value 3 Checkbox} = 0
      ),
      {Value 1}
    )
  )
)

You can find a working version here. To view the formulas, duplicate the base by clicking the title of the base at the top of the screen, then the three horizontal dots on the right, and then the “Duplicate Base” button.

Nice! thanks for the quick response. This works great.

At this point (brand new) no way I would intuitively come up with that logic / syntax on my own. You have any links to tutorials that can walk me through how this is put together?

I have more calculations I’ll need to make that probably use similar logic.

No worries. I’m afraid I don’t know of any tutorials, sorry

Hopefully someone else in the forums suggest something for you!