Does @row work in a summary field?

03/17/21
Answered - Pending Review

I am trying to return a date from a created column based on whether a multiselect dropdown contains a certain value.

I'm using If and Has but when I refer to the Created column it does not work

Answers

  • Heather DHeather D ✭✭✭✭✭

    Hi Roy,


    Have you tried using COUNTIF(CONTAINS?


    It may be helpful to provide screenshots showing what you're trying to summarize, with any sensitive data blocked out.


    Best,

    Heather

  • Andrée StaråAndrée Starå ✭✭✭✭✭

    Hi @Roy Bass

    I hope you're well and safe!

    No, @row doesn't work with the Summary Section because it references the same row it's on, and in the Sheet Summary, there isn't one.

    Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, [email protected])

    I hope that helps!

    Be safe and have a fantastic week!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • Thanks, attached is a screen shot of my sheet. The "Order Chasing" column in the sheet is a multi select dropdown. I am trying to get a summary field to show the created date of the latest row that has the customer name in the multi select dropdown.

  • jmojmo ✭✭✭✭✭

    Following...

    I have the same question - I need to count the number of column rows that contain 2019, 2019 or 2013.

    I tried this for just 2013 but it doesn't work (INVALID OPERATION):

    =COUNTIF([3. Which MS Exchange Server version(s)?]:[3. Which MS Exchange Server version(s)?], CONTAINS("Microsoft Exchange Server 2013"))

  • Hi @jmoser

    You're missing an element of the CONTAINS function - you need to specify where it's looking for your criteria. In this instance, you're looking for that value in the cells of the previously stated range, so you can use the @cell function to state this.

    Try this:

    =COUNTIF([3. Which MS Exchange Server version(s)?]:[3. Which MS Exchange Server version(s)?], CONTAINS("Microsoft Exchange Server 2013", @cell))

    Cheers!

    Genevieve

  • Hi @Roy Bass

    Using the HAS function is correct for looking in a multi-select dropdown. It sounds like you'll also want to use the MAX function to look for the Max Date (or latest date) in the Date Created column.

    Try an MAX(COLLECT function, like so:

    =MAX(COLLECT([Created Date]:[Created Date], [Order Chasing]:[Order Chasing], HAS(@cell, "Customer Name"))


    Let me know if this works for you!

    Cheers,

    Genevieve

  • Hi Genevieve, thanks for that, I am getting an unparseable error, is this becuase the @cell won't work in the summary field?

  • Hi @Roy Bass

    No, @cell will work in a Summary Field. Did you adjust the formula to match your sheet/columns/values, or did you copy and paste?

    I see that your date column is called "Created" not "Created Date" like in my example.

    Try this?

    =MAX(COLLECT(Created:Created, [Order Chasing]:[Order Chasing], HAS(@cell, "Customer Name"))

    If you're still getting an error, it would be helpful to see a screen capture showing the formula & confirming your columns.

    Cheers!

    Genevieve

  • I did adjust to match my columns. When nothing is in the Order Chasing column it shows 0 as you'd expect. But as soon as you put a name in it says #Invalid Column Value. I have checked the names match in the formula and the column, and that we don't have any extra spaces etc. Sorry I can't share the screenshot as it now has some customer data in.

    Thanks

  • I have resolved this now, I just needed to change the column type in the summary field to Date. Thanks for your help

  • Hi @Roy Bass

    I'm glad you figured it out! Yes, since you are looking for a date in the "Created" column, your summary field will also need to be a date-type of field.

    Cheers,

    Genevieve

Sign In or Register to comment.