Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Jan 29, 2018 11:58 PM
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!!
Jan 30, 2018 12:22 AM
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é
Jan 30, 2018 12:58 AM
@Andre_Zijlstra beat me to it. :slightly_smiling_face:
Jan 30, 2018 01:11 AM
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…
Jan 30, 2018 02:34 AM
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?
Jan 30, 2018 03:13 AM
Sure do. For me it’s more to do with programming languages than Dutch though. :winking_face:
Jan 30, 2018 04:57 AM
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.
Jan 30, 2018 09:58 AM
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:
Jan 30, 2018 11:17 AM
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.
Jan 30, 2018 07:59 PM
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: