Best Of

Hi @Beth Keane

As I understand it, a cell could include up to 10 events, and they fall into 3 categories:

• Group 1 ignore (in my example these are events 1-3)
• Group 2 multiply by 2 other columns (in my example these are events 4-6)
• Group 3 multiply by 1 other column (in my example these are events 7-10)

If a cell has 2 events (say event 9 and event 10) and you use a COUNTIF with an OR, that formula will return 1. The cell contains either event 9 or event 10.

If you want this to return 2 (the total number of events in the group), then you need to count each separately and add them together.

Here column 5 shows the result of the formula in column 6.

So, to count the four events in Group 3, the formula would look like this (with "event n" replaced with the value in your drop down).

=SUM(COUNTIF([Additional Experiences & Enhancement]@row , HAS(@cell , "Event 10")), COUNTIF([Additional Experiences & Enhancement]@row , HAS(@cell , "Event 9")), COUNTIF([Additional Experiences & Enhancement]@row , HAS(@cell , "Event 8")), COUNTIF([Additional Experiences & Enhancement]@row , HAS(@cell , "Event 7")))

You would then multiply this by the number of attendees to get the value.

That formula would then be:

=(SUM(COUNTIF([Additional Experiences & Enhancement]@row , HAS(@cell , "Event 10")), COUNTIF([Additional Experiences & Enhancement]@row , HAS(@cell , "Event 9")), COUNTIF([Additional Experiences & Enhancement]@row , HAS(@cell , "Event 8")), COUNTIF([Additional Experiences & Enhancement]@row , HAS(@cell , "Event 7")))) * Attendees@row

You can then repeat this for group 2, multiplying that total by the two columns.

I hope this is helpful.