FIND that searches multiple fields?

Hi everyone,

I’m using Airtable to help reformat some .csv attendance sheets that are a pain. There is only one export option that comes in as Person ID for the primary field, then each event comes in as a text field with an X to mark attendance.

I’m going for a way to count total # of attended events in a month. I’ve been using the FIND function to find “X” and give me a total, but I haven’t figured out the correct way to FIND across multiple fields (ie - all January events, etc.). I’m new to formulas, so I’m not even sure I can.

This works fine for a single event, but I need to find a way to include multiple event fields.

FIND(“X”, {Jan 8, 2019 AM Meeting})

Thoughts are appreciated! Thanks!

…and now I’ve figured out how to make nested FIND formulas within a SUM formula.

For the record, and in hopes it helps someone else, I did…

SUM(
(FIND(“X”, {Jan 8, 2019 AM Meeting})),
(FIND(“X”, {Jan 8, 2019 PM Meeting})),
(FIND(“X”, {Jan 15, 2019 AM Meeting})),
(FIND(“X”, {Jan 15, 2019 PM Meeting})))

Alternatively, as long as your meting fields are either 'X' or empty, you could use COUNTA():

COUNTA(A,B,C,D)

Or, in your case

COUNTA(
    {Jan 8, 2019 AM Meeting},
    {Jan 8, 2019 PM Meeting},
    {Jan 15, 2019 AM Meeting},
    {Jan 15, 2019 PM Meeting}
    )

This also avoids possible problems if someone enters ' X' (leading space) or 'x' (wrong case) — the former would result in an overcount, the latter in an undercount. :wink:

1 Like