Count new event attendees with a formula

Levi

Hello,

I work for an event based organization. Each of our events belongs to three separate programs (represented "M", "X" and "S" in my formula). We track all of our yearly attendance data in one master sheet with separate columns for program type, event type and each attending group's attendance at the event. All column types involved are text/number.

I'm pulling some metrics on our new event attendees into another sheet and I'd like to identify the number of people who attended Program M for the first time in 2023 AND have also attended at least one of our other programs in the last three years.

The first part of the formula is straight forward. SUMIFS the 2023 attendance column for Program M if the 2023 event column is not blank AND the event column for each other year for Program M is blank:

=SUMIFS({M23#}, {M23E}, "<>", {M22E}, "", {M21E}, "", {M20E}, "", {M19E}, "", {M18E}, "", {M17E}, "", {M16E}, "", {M15E}, "")

It's the next part that I can't figure out. Identifying how many of these new attendees have also attended one of the following events (without duplicating attendees): {X22E}, {X21E}, {X20E}, {S22E}, {S20E}, {S19E}.

I've been attempting to use an OR function but I think I've realized that an OR function cannot be applied to multiple columns - only multiple criteria within a single column??

I hope that makes sense! Any suggestions?

Levi

I think I've found a simple solution... kicking myself for not thinking of it sooner.

2 helper columns:

1. SUMIFS all those who attended M for the first time in 2023. =SUMIFS({M23#}, {M23E}, "<>", {M22E}, "", {M21E}, "", {M20E}, "", {M19E}, "", {M18E}, "", {M17E}, "", {M16E}, "", {M15E}, "")
2. SUMIFS all those who attended M for the first time in 2023 AND have never attended an X or S event. =SUMIFS({M23#}, {M23E}, "<>", {M22E}, "", {M21E}, "", {M20E}, "", {M19E}, "", {M18E}, "", {M17E}, "", {M16E}, "", {M15E}, "", {X22E}, "", {X21E}, "", {X20E}, "", {S22E}, "", {S20E}, "")

Then, just subtract column 2 from column 1. This should give me the total of those who attended M for the first time but have also attended X and S at some point in the past. I've double checked it with a report and I think it works!

Thanks again @KPH - helper columns the right call!

KPH

Hi @Levi

I think you are really close to what you want. You cannot use the OR across multiple columns as you originally wanted, but you can create (and then hide) a helper column to bring all the information you want into one column and then use that in your formula.

That sentence might be all you need to nudge you in the right direction but as I am not 100% sure I understand exactly what you are doing, I am going to take some time to write out everything from the beginning (including the bit you have covered) so that you can reject any incorrect assumptions I have made and for anyone else following along.

Part 1 - How many attended their first M in 23?

This is a snippet of what I believe the first sheet looks like, with one row per attendee. I haven't put in all the events, and am going to work on this sheet rather than across sheets so we can see what we're doing. Levi will obviously need to change the cell references to cross sheet references. I have named the columns the same name Levi uses in their cross sheet reference.

Levi's SUMIFS formula is summing all the numbers (in my case 1's) in the M23E column if that column is not blank and the other columns M22E, M21E, M20E are blank.

The data might be for groups of people rather than individuals and this will still work. Though I get confused about how we know if the attendee in each group was also part of the previous group.

You may not need this part in the formula

[M23E]:[M23E], "<>"

If the cell is blank there will be nothing to sum.

Part 2 - How many also attended an X or S event in the past

To add an OR part to your SUMIFS including multiple columns you can add a helper column. Here I have added one called Any X

You can use an IF formula to populate this with 1 if the attendee has attended any of the X events, and blank if they have not. For my two columns, I have:

=IF(OR([X22E]@row > 0, [X20E]@row > 0), "1", "")

Levi, you could do "Any X or Any S" or keep these as two separate columns (keeping them separate will mean you can reuse them, for example if you want to see if new X attendees also attended S or M in the past). You'll need to extend the formula to include the other years.

And now, you can use that new column in your original formula as the final condition in your SUMIFS

=SUMIFS([M23E]:[M23E], [M23E]:[M23E], "<>", [M22E]:[M22E], "", [M21E]:[M21E], "", [M20E]:[M20E], "", [Any X]:[Any X], "<>")

Now, in this example the answer is 1. Mr Smith is the only person to attend M for the first time in 23 and to have previously attended X.

Levi

@KPH Thank you for your thoughtful response! I think you're right that I will need to utilize helper columns for this. Apologies, I think your solution could be possible if I had not left out some important details:

• I do not have the ability to make edits or add columns to the source document - it's too full already! I'm trying to build formulas that will pull data from the source sheet.
• Our events are attended primarily by groups rather than individuals. We track groups and group totals in each row. The goal is to know the total of all of the group totals that attended M for the first time in 23 and have also attended X or S at least once in the past.

KPH

OK,

For constraint 1

• "I do not have the ability to make edits or add columns to the source document - it's too full already! I'm trying to build formulas that will pull data from the source sheet."

Two options here:

Option 1 - create a helper sheet and use cross sheet formula to pull in just the columns you need for this analysis and then analyze that sheet.

Option 2 - change the formula so that rather than summing all the rows that match your criteria you sum those that match and sum those that don't match and then subtract them, all within one column.

Constraint 2

• "Our events are attended primarily by groups rather than individuals. We track groups and group totals in each row. The goal is to know the total of all of the group totals that attended M for the first time in 23 and have also attended X or S at least once in the past."

This is totally fine. You could have numbers greater than 1 in your sheet and a row per group. Everything in the first answer was designed to work just in case that is your situation. I just couldn't figure out how you'd know if a person attended previously if you had a group per row. But if you only want to know if the group attended, and then do the math on the size of the group, then you're all good.

If the data looks more like this:

This returns 27 (the size of mr smith's group at M23E, only)

=SUMIFS([M23E]:[M23E], [M23E]:[M23E], "<>", [M22E]:[M22E], "", [M21E]:[M21E], "", [M20E]:[M20E], "", [Any X]:[Any X], "<>")

Levi

I think I've found a simple solution... kicking myself for not thinking of it sooner.

2 helper columns:

1. SUMIFS all those who attended M for the first time in 2023. =SUMIFS({M23#}, {M23E}, "<>", {M22E}, "", {M21E}, "", {M20E}, "", {M19E}, "", {M18E}, "", {M17E}, "", {M16E}, "", {M15E}, "")
2. SUMIFS all those who attended M for the first time in 2023 AND have never attended an X or S event. =SUMIFS({M23#}, {M23E}, "<>", {M22E}, "", {M21E}, "", {M20E}, "", {M19E}, "", {M18E}, "", {M17E}, "", {M16E}, "", {M15E}, "", {X22E}, "", {X21E}, "", {X20E}, "", {S22E}, "", {S20E}, "")

Then, just subtract column 2 from column 1. This should give me the total of those who attended M for the first time but have also attended X and S at some point in the past. I've double checked it with a report and I think it works!

Thanks again @KPH - helper columns the right call!