Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.
Reference Entire Column in Function
Hello Team,
I wish to reference an entire column in a function, for example:
Current Function:
=SUMIF([Task Owner]1:[Task Owner]82, "Text", [Resource Sum Column]1:[Resource Sum Column]82) / 100
Desired Function:
=SUMIF([Task Owner]:[Task Owner], "Text", [Resource Sum Column]:[Resource Sum Column]) / 100
Is this possible? I don't wish to rely on specific row numbers in case my sheet grows/shrinks. Thanks for your help.
Comments
-
Looks OK to me.
What's the problem?
https://help.smartsheet.com/articles/775363-using-formulas#sumif
Craig
-
Was there a fix for this? I get #NO MATCH even though the help looks like this should be fine. I can use a hard set range including up to the last row I have populated with data and it works. As soon as I take the row references away to include the whole column,I get the error. Obviously I don't want to change the formula when the number of rows in my data changes.
=SUMIFS([Amount to Bill]:[Amount to Bill], [Department]:[Department], =[Info 1]14)
-
Ok, I figured it out. The Department column was being populated from a vLookup. If there was even one "NO MATCH" on the vLookup, the SUMIFS would fail with same error - this includes no match error you get when you copy the formula below the data. I added an IFERROR statement to the VLookup to fix the issue.
=IFERROR(VLOOKUP(Item330, [Department Lookup 1]$1:[Department Lookup 2]$24, 2, false), "..")
-
Is there any possibility to sum for an entire range of column rather than specific cell range.
-
Column reference looks like this:
[Column Name]:[Column Name]
So COUNT([Column Name]:[Column Name]) will count the items in the column.
You did not ask, but you can also reference multiple columns:
=COUNT([Column A Name]:[Column C Name])
but this is less often seen because it has fewer uses and can be messed up by reordering of the columns.
Craig
-
How do I reference a partial column? In Excel, I could do COUNT(B2:B) so that I could skip the top header row and catch the rest of the column. Doesn't seem to work in Smartsheet?!
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives