IF Statement result brings back another cell value

Good morning everyone!

I am fairly new to Smartsheet so please spare my dumb question but I really need the help of the experts solving a formula logic:

Scenario:

I have a list of Events, Attendees and a column with a IF = Month(Today()) formula based on the event date to know what Events took place in the current Month.

Desired result:

On a Dashboard, use 2 Metric widgets to display which Event in the Current Month had the most Attendees and which Event in the Current Month had the less Attendees.

Can you please help me build the logic and functions for both cases?

I tried something like

IF(AND({Current Month? Range} = "Yes", {Number of Attendees Range} = MAX(()),

And this is where I am having problems telling the formula to give me the value of the Event Name column.

Please excuse me if I am not using the correct statements.


JS

Best Answers

  • John Pudar
    John Pudar ✭✭✭✭
    Answer ✓

    Hi @Jorge Silva,

    Not a "dumb" question at all! There may be a simpler way to get the same result, but I think this might work for you for the highest attended event:

    =INDEX([Event Name]:[Event Name], MATCH(MAX(COLLECT([Number Of Event Attendees]:[Number Of Event Attendees], [Current Month]:[Current Month], ="Yes")), [Number Of Event Attendees]:[Number Of Event Attendees], 0))

    And for the lowest attended event, try:

    =INDEX([Event Name]:[Event Name], MATCH(MIN(COLLECT([Number Of Event Attendees]:[Number Of Event Attendees], [Current Month]:[Current Month], ="Yes")), [Number Of Event Attendees]:[Number Of Event Attendees], 0))

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    edited 06/24/21 Answer ✓

    @Jorge Silva

    Please try the following formula for ( Number of Event Attendees )

    Event Score =IF(AND([Current Month]@row = "Yes", MAX([Number of Event Attendees]:[Number of Event Attendees]) = [Number of Event Attendees]@row), "Highest", IF(AND([Current Month]@row = "Yes", MIN([Number of Event Attendees]:[Number of Event Attendees]) = [Number of Event Attendees]@row), "Lowest"))

    and convert it to column format formula, the following screenshot shows the result:

    you can test the soluation in this puplished Sheet link:

    and the formula that check the current month is ( change it to column format formula )

    Current Month =IF(MONTH(TODAY()) = MONTH([Event Start Date]@row), "Yes", "No")

    and use the following conditional formating to highlight the row


    PMP Certified

    [email protected]

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

Answers

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭

    Hi @Jorge Silva

    Hope you are fine, Please add a copy of your sheet or a screenshot (after removing or replacing any sensitive information). This will make it easier for me to provide the appropriate answer to your question.

    PMP Certified

    [email protected]

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Hello @Bassam Khalil

    Many thanks for replying. See below an example of my sheet:

    I would like a formula to give me the Event Name based on: first, is in the Current Month and then, the number of Event Attendees is the highest (in the current Month). The same on another formula but the other way around, the Event Name with the lowest Number of Attendees (in the current month).

    The idea behind that is to automate a dashboard so with each passing month it gives me the Events with more and less attendees, without me having to go each month and change the source cell manually.


    I hope this helps.


    Many many thanks in advance.


    JS

  • John Pudar
    John Pudar ✭✭✭✭
    Answer ✓

    Hi @Jorge Silva,

    Not a "dumb" question at all! There may be a simpler way to get the same result, but I think this might work for you for the highest attended event:

    =INDEX([Event Name]:[Event Name], MATCH(MAX(COLLECT([Number Of Event Attendees]:[Number Of Event Attendees], [Current Month]:[Current Month], ="Yes")), [Number Of Event Attendees]:[Number Of Event Attendees], 0))

    And for the lowest attended event, try:

    =INDEX([Event Name]:[Event Name], MATCH(MIN(COLLECT([Number Of Event Attendees]:[Number Of Event Attendees], [Current Month]:[Current Month], ="Yes")), [Number Of Event Attendees]:[Number Of Event Attendees], 0))

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    edited 06/24/21 Answer ✓

    @Jorge Silva

    Please try the following formula for ( Number of Event Attendees )

    Event Score =IF(AND([Current Month]@row = "Yes", MAX([Number of Event Attendees]:[Number of Event Attendees]) = [Number of Event Attendees]@row), "Highest", IF(AND([Current Month]@row = "Yes", MIN([Number of Event Attendees]:[Number of Event Attendees]) = [Number of Event Attendees]@row), "Lowest"))

    and convert it to column format formula, the following screenshot shows the result:

    you can test the soluation in this puplished Sheet link:

    and the formula that check the current month is ( change it to column format formula )

    Current Month =IF(MONTH(TODAY()) = MONTH([Event Start Date]@row), "Yes", "No")

    and use the following conditional formating to highlight the row


    PMP Certified

    [email protected]

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Many thanks @John Pudar and @Bassam Khalil for your answers! Both worked and I will apply them for different approaches in the context of the final report and dashboard.

    😊

    Thank You!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!