Join IF Contact Type = Customer

I have a sheet with the following columns:

Contact Type | Email

In the Sheet Summary, I have a field where I would like to create an email string that can easily be used to paste a bunch of email separated by a semi-colon for easy sharing.

I want to join all emails where Contact Type is "Customer" into a single field with the email separated by a ";" and if the email field is blank, I want it ignored.

Not sure if I would set this up as an IF with a JOIN or a JOIN with an IF.

I have this so far, but it's not working: =IF(AND([Contact Type]@row = "Customer", email@row <> ""), JOIN(Email:Email, " ;"))

Thank you in advance

Find more posts tagged with

Formulas

Accepted answers

JamesB

@JSpears

Maybe something like this?

=JOIN(COLLECT(Email:Email, [Contact Type]:[Contact Type], ="Customer", Email:Email, <>""), ";")

All comments

JamesB

@JSpears

Try This:

=(IF(AND([Contact Type]@row = "Customer", Email@row <> ""), [Contact Type]@row + ";" + Email@row))

JSpears

@JamesB - that gave me #unparseable

JamesB

sorry, I realized that I have a couple of extra parentheses.

=IF(AND([Contact Type]@row = "Customer", Email@row <> ""), [Contact Type]@row + ";" + Email@row)

I just tested this in a third column and it is working, but only on a per row basis. Based on what I am reading in your original post, you are looking to collect all of the cells that meet these criteria into one cell?

JamesB

@JSpears

Maybe something like this?

=JOIN(COLLECT(Email:Email, [Contact Type]:[Contact Type], ="Customer", Email:Email, <>""), ";")

Paul Newcome

You will need a JOIN/COLLECT combo for this:

=JOIN(COLLECT(Email:Email, Email:Email, @cell <> "", [Contact Type]:[Contact Type], @cell = "Customer"), "; ")

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.