I want to create a column formula that will sum all columns in a range only if the column's name contains the word "ACTUAL" ? Is this possible? I can't find any functions that do this and I tried doing look ups for row #0 or row #-1 which sometimes works in other systems to reference table headers.
Hey @gzimmer2,
I don't believe you can use column headers as comparable data in Smartsheet unfortunately.
If my response was helpful in any way (or answered your question) please be sure to upvote it, mark it as awesome, or mark it as the accepted answer!
I'm always looking to connect with other industry professionals, feel free to connect with me on LinkedIn as well!
Hi,
I hope you're well and safe!
Unfortunately, it's not possible now, but it's an excellent idea!
Please submit this as a Product Feedback or Idea (If it hasn't been added already) when you have a moment.
Here's a possible workaround or workarounds
- Add a so-called helper row, row 1 or wherever it makes sense and add the column names and then you can reference that row in the formulas.
Make sense?
Would that work/help?
I hope that helps!
Be safe, and have a fantastic week!
Best,
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
✅ Did my comment(s) help/answer your question or solve your problem? Please support the Community and me by marking it - Insightful 💡- Vote Up ⬆️ - Aweseome ❤️ - or/and as the accepted answer. It will make it easier for others to find a solution or help to answer! I appreciate it, thanks!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
I'm trying to work through a "helper row" method with this formula:
=SUMIF([Nov 2021]1:[Mar 2030]$1, "ACTUAL", [Nov 2021]@row:[Mar 2030]@row)
This works but only as a cell level formula, I'd really like it to find a column level method that works
Excellent!
How many columns do you have?
✅ Remember! Did my comment(s) help/answer your question or solve your problem? Please support the Community and me by marking it - Insightful 💡- Vote Up ⬆️ - Aweseome ❤️ - or/and as the accepted answer. It will make it easier for others to find a solution or help to answer! I appreciate it, thanks!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
I have at least one column for every month between Nov 2021 and March 2030. Right now that's 103 columns that I need to be checking if the first row = "ACTUAL". Are you suggesting that I hard code all of those column names into the formula for it to work as a column formula?
Ok. No, my thought and what I've done is using the Sheet Summary section, but I wouldn't recommend that for that many columns.
You can instead use the INDEX feature.
✅ Remember! Did my comment(s) help/answer your question or solve your problem? Please support the Community and me by marking it - Insightful 💡- Vote Up ⬆️ - Aweseome ❤️ - or/and as the accepted answer. It will make it easier for others to find a solution or help to answer! I appreciate it, thanks!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
