Save the date! Join us on October 16 for our Product Ops launch event. Register here.
May 29, 2019 04:22 PM
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!
May 29, 2019 04:53 PM
…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})))
May 29, 2019 08:05 PM
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. :winking_face: