Best Of

Hi @Monika_923

First, I obtain the Transfer date using the same INDEX(MATCH()) structure as the previous one.

[Transfer date] =IFERROR(INDEX({CC&Names : Transfer Date}, MATCH([Employee name]@row , {CC&Names Range : Name}, 0)), "")

https://app.smartsheet.com/b/publish?EQBCT=1e62378b8fb34058b4ea14e76db1cbf0

Then, using the [Transfer date] helper column, I used the following logic to get the Cost Center.

• [Transfer date] is a date?
  • Yes
    • [Transfer date] is on or before today?
      • Yes -> Get a New Cost Center from the CC&Names sheet.
      • No -> Get a current Cost Center from the CC&Names sheet.
  • No -> Get a current Cost Center from the CC&Names sheet.

[Cost Center] =
IF(ISDATE([Transfer date]@row), 
  IF([Transfer date]@row  <= TODAY(), 
    JOIN(COLLECT({CC&Names Range : Next Cost Center}, {CC&Names Range : Name}, [Employee name]@row ), ""), 
    JOIN(COLLECT({CC&Names Range : Cost Center}, {CC&Names Range : Name}, [Employee name]@row ), "")), 
JOIN(COLLECT({CC&Names Range : Cost Center}, {CC&Names Range : Name}, [Employee name]@row ), ""))

I used this time the JOIN(COLLECT()) functions structure to avoid IFERRORs. So, the Cost Center is now a text value, but I hope that is not a problem.

Below is the same structure formula without using the [Transfer date] helper column. I think the former is easy to maintain, and I recommend it unless you prefer not to have an additional helper column.

[Cost Center Without Helper (Number)] =

IF(ISDATE(IFERROR(INDEX({CC&Names : Transfer Date}, MATCH([Employee name]@row , {CC&Names Range : Name}, 0)), "")), IF(IFERROR(INDEX({CC&Names : Transfer Date}, MATCH([Employee name]@row , {CC&Names Range : Name}, 0)), "") <= TODAY(), JOIN(COLLECT({CC&Names Range : Next Cost Center}, {CC&Names Range : Name}, [Employee name]@row ), ""), JOIN(COLLECT({CC&Names Range : Cost Center}, {CC&Names Range : Name}, [Employee name]@row ), "")), JOIN(COLLECT({CC&Names Range : Cost Center}, {CC&Names Range : Name}, [Employee name]@row ), ""))

https://app.smartsheet.com/b/publish?EQBCT=e9b63c6a60e048af83b217a16a0946a2

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.