How to Sum 3 amounts based on status conditions with IF statements

Bradley_Ellison · ‎Jan 29, 2018

Hi All,

I wonder if you might be able to please help me, I am trying to create a formula to sum amounts based on the status of a persons deal (think sales pipeline for lines of business). I’m trying to Sum a series of If statements (I think!)

As you can see from this example, there are 3 types of incomes, and each person/customer might be Open or Closed under each line of business.

So what I am trying to do is Sum all income types If the status is Open (I.e. to create a Total amount for Open deals). In this example Person 1 is only income type A and Open, so the desired outcome is to produce a $1,000 result. Person 2 has income in 2 lines of business, but 1 is closed, so I want it to only sum the Open one, again $1,000. But person 3 has income from lines of business A and C, and both are Open, so I want it to have a $2,000 sum.

I have tried with If, Sum and Or statements, but I just have no idea what the logic is I’m supposed to use for what may be fairly straight forward for an experienced formula user.

Using IF({A Status}=‘Open’,{A Income}) returns just the A Income field, how can I get it to check for Open status in B and C, and then Sum all the incomes together?

Any help would be greatly appreciated!!

Andre_Zijlstra · ‎Jan 30, 2018

It actually is very simple: you are allowed to add the same IF formula at the end of your formula. And repeat it. If would look like this:

IF({A Status}=“Open”,{A Income})+IF({B Status}=“Open”,{B Income})+IF({C Status}=“Open”,{C Income})

Just try it!

Regards,
André

Tuur · ‎Jan 30, 2018

@Andre_Zijlstra beat me to it. :slightly_smiling_face:

Bradley_Ellison · ‎Jan 30, 2018

Thank you Andre!

It didn’t work initially, but I changed the double quote to single and it worked then.

IF({A Status}=‘Open’,{A Income})+IF({B Status}=‘Open’,{B Income})+IF({C Status}=‘Open’,{C Income})

I thought I had to SumIf, but the simplicity of multiple If + statements is great.

Thanks again Andre and Tuur…

Andre_Zijlstra · ‎Jan 30, 2018

Good you mention that. I’m from the Netherlands, and over here we use double quotes a lot.
@Tuur you are funny :slightly_smiling_face:
Btw, do you have the same issue with the double quotes?

Tuur · ‎Jan 30, 2018

Sure do. For me it’s more to do with programming languages than Dutch though. :winking_face:

W_Vann_Hall · ‎Jan 30, 2018

To date, I’ve not found an instance where single and double quotes cannot be used interchangeably — which is not to say one doesn’t exist, but only that I’ve not yet stumbled across it.

In this instance, I suspect the problem had to do with copy/pasting your formula, as your "/" pairs had been pretty-printed to inverted comma-style “/” by the forum software. Replacing them manually with single quotes would have substituted (correctly) '/' pairs.

For a single-line formula, if you precede and follow it with a back-tick,[1] the forum software will leave your single and double quotes alone, allowing copy/paste to work correctly. For multi-line formulas, precede and follow the block of code with three backticks ('```') on a line by themselves.
. __________

[1] On US keyboards, the back-tick character — '`', which the rest of the world sees as a grave accent — is the key to the left of the '1' key, unshifted. On US International and, presumably, Dutch keyboards, one uses the two-stroke combination of [grave accent][spacebar] to generate a back-tick.

Andre_Zijlstra · ‎Jan 30, 2018

I was kinda curious if the single quotes would work in my formula, and it sure did:

On my keyboard, left from the Return key (above my right shift key) is the Single Quote. With Shift I get a double quote.

I will make sure that I use single quotes in the future :slightly_smiling_face:

W_Vann_Hall · ‎Jan 30, 2018

Your formula also works with straight double quotes ('"'). :slightly_smiling_face:
It just doesn’t work with curly quotes.

Oh, and since I wrote that awfully confusing paragraph about ‘back-ticks’ and grave accents, I’ve learned one can accomplish the same thing by prefixing lines intended to be code with four (4) space characters. That is, both

`BOTH 'SINGLE QUOTES' AND "DOUBLE QUOTES" SHOULD BE STRAIGHT`

and

˽ ˽ ˽ ˽ BOTH 'SINGLE QUOTES' AND "DOUBLE QUOTES" SHOULD BE STRAIGHT

— where '˽' represents a space character — will result in

BOTH 'SINGLE QUOTES' AND "DOUBLE QUOTES" SHOULD BE STRAIGHT

being displayed — and able to be copy-and-pasted as a valid Airtable formula.

Bradley_Ellison · ‎Jan 30, 2018

I just checked this too and found that the straight double quotes worked, curly quotes didn’t!

Interesting, thanks again for all your help gents… :grinning: