IF AND Formula w/ multiple columns


#1

Hi -

I am trying to make a formula that will indicate if Columns A, B and C are not blank, then Column D would be filled in. I was able to get it to work with the simple formula of one column, but can’t figure out how to write it to check multiple columns.

What I’m trying to get it to do:

IF

{Event Name} = “” and
Location = “” and
{Start Date} = “” and
{End Date} = “”

then put “Send” in formula column.

Thanks in advance - would love help in learning how to write this out properly.


#2

Hi @Jeannie_Ruesch

So, if all 4 of the fields are empty, then the word “SEND” needs to be displayed in the new formula field.
That would be:

IF(
         AND(
                       {Event Name} = BLANK(),
                       {Location} = BLANK(),
                       {Start Date} = BLANK(),
                       {End Date} = BLANK() 
                       ),
         "SEND"
          )

#3

Jeannie -

One minor change to what @Andre_Zijlstra provided: The next to last line should read "SEND" and not “SEND”. (The difference is subtle: Look at the style of quotation marks around the word.) This is only a problem if you mark-and-copy his formula; if you were to retype it, it would work fine.

@Andre_Zijlstra

If you precede and follow your formula text with three backticks (aka accent grave), as so —

```

— both the correct quotation style and indentation are preserved:

IF(
    AND(
        {Event Name} = BLANK(),
        {Location} = BLANK(),
        {Start Date} = BLANK(),
        {End Date} = BLANK()
        ),
    "SEND"
    )

#4

@W_Vann_Hall
Thank you for taking time for this topic (again). It seems my Dutch keyboard could be reason for some confusion here.

This Quotes key:
Key%20Quote
is left of the key. I tried both options in the formula (“SEND”, and ‘SEND’) and both rendered the same result.

Another key (left of the <1>:
Key%20Quote%20alike
did not work. This seems to be the “Grave” key? And that is the one I should use preceding and following the formula text?

So, the backtick is not to be used in the formula text, but preceding and following the formula.
In the formula I should use the keystroke in stead of "?


#5

On European keyboards, the grave key is usually a dead key, used as the first stroke in a two-key sequence: [grave]+e = è. In such cases, there is a way to generate the grave character by itself, either [grave][grave] or [grave][spacebar], if I recall correctly.

In Markdown (which this forum uses), you can trigger code mode one of three ways:

  1. A single backtick (grave) before and after the text to be formatted as code
  2. Triple backticks on a line by themselves before and after the section of text to be formatted as code
  3. Four space characters (beginning at the left margin) before a line of text to be formatted as code

Unfortunately, method 1 does not support indentation, as multiple space characters are rendered as a single space. (I’d through method 3 did not, as well, but this proved wrong.) For instance, all three of the following examples are the same, except for how code mode is triggered:

Single backtick before and after each line
IF(
AND(
{Event Name} = BLANK(),
{Location} = BLANK(),
{Start Date} = BLANK(),
{End Date} = BLANK()
),
"SEND"
)

Four spaces before each line
(Note, there must be a blank line before the first code line.)

IF(
    AND(
        {Event Name} = BLANK(),
        {Location} = BLANK(),
        {Start Date} = BLANK(),
        {End Date} = BLANK()
        ),
    "SEND"
    )

Three backticks before and after excerpt

IF(
    AND(
        {Event Name} = BLANK(),
        {Location} = BLANK(),
        {Start Date} = BLANK(),
        {End Date} = BLANK()
        ),
    "SEND"
    )

As you can see, the final method places the code excerpt in its own scrollable window and performs syntax-specific highlighting. (Based on what language, I’m not certain; most Markdown implementations don’t highlight unless a language is specified after the first set of backticks.)

If you can’t tweeze a backtick character out of your keyboard, you can use the four-space method of triggering code mode.

Here is a handy Markdown reference.

Edit: Well, actually you can’t see the third method places the code in its own scrollable window, as the code excerpt wasn’t long enough.


#6

Thank you. The cheatsheet helped a lot as well.


#7

Thank you so much for this. Very simple and easy to understand. I really appreciate your help.

If I wanted to swap this around, so the formula is if those columns are NOT blank how would I do that?


#8

Thank you for the formula! Much appreciated and worked great with the modification mentioned below.


#9

If I wanted to swap this around, so the formula is if those columns are NOT blank how would I do that?

You could use

IF(
    AND(
        {Event Name} != BLANK(),
        {Location} != BLANK(),
        {Start Date} != BLANK(),
        {End Date} != BLANK()
        ),
    "SEND"
    )

or

IF(
    NOT(
        OR(
            {Event Name} = BLANK(),
            {Location} = BLANK(),
            {Start Date} = BLANK(),
            {End Date} = BLANK()
            )
        ),
    "SEND"
    )

Either should do.