Does @row work in a summary field?

Roy Bass

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

Heather Duff

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å

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, andree@workbold.com)

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!

Roy Bass

Smartsheet.JPG

Uploaded Mar 18, 202174.46 KB

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.

jmo

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"))

Genevieve P.

jmo

Mar 19, 2021

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

Genevieve P.

Roy Bass

Mar 18, 2021

https://us.v-cdn.net/6031209/uploads/UTVU3DPMKC77/smartsheet-jpg.jpg

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.

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

Roy Bass

Genevieve P.

Mar 22, 2021

https://community.smartsheet.com/discussion/comment/284041#Comment_284041

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?

Genevieve P.

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

Roy Bass

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

Roy Bass

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

Genevieve P.

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