Skip to main content

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!!

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é

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é


@Andre_Zijlstra beat me to it. 🙂

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é


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…

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…


Good you mention that. I’m from the Netherlands, and over here we use double quotes a lot.

@Tuur you are funny 🙂

Btw, do you have the same issue with the double quotes?

Good you mention that. I’m from the Netherlands, and over here we use double quotes a lot.

@Tuur you are funny 🙂

Btw, do you have the same issue with the double quotes?


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

Good you mention that. I’m from the Netherlands, and over here we use double quotes a lot.

@Tuur you are funny 🙂

Btw, do you have the same issue with the double quotes?



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,h1] 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 bgrave accent]ospacebar] to generate a back-tick.


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,a1] 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.

. __________


p1] 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 ngrave accent]espacebar] to generate a back-tick.


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 🙂

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 🙂


Your formula also works with straight double quotes ('"'). 🙂

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.

Your formula also works with straight double quotes ('"'). 🙂

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.


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… 😀

Reply


Terms & Conditions