Skip to content
  • Try for free

    • Archived 2017 Posts

    Archived 2017 Posts

    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

    ✭✭
    edited 12/09/19 in Archived 2017 Posts

    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.

    Tags:
    · Share on FacebookShare on Twitter

    Sign in
    Try for free

    Comments

    • ✭✭✭✭✭✭

      Looks OK to me.

      What's the problem?

       

      https://help.smartsheet.com/articles/775363-using-formulas#sumif

       

      Craig

       

      · Share on FacebookShare on Twitter
    • ✭✭

      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)

      · Share on FacebookShare on Twitter
    • ✭✭

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

      · Share on FacebookShare on Twitter
    • ✭✭

      Is there any possibility to sum for an entire range of column rather than specific cell range.

      · Share on FacebookShare on Twitter
    • ✭✭✭✭✭✭

      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

      · Share on FacebookShare on Twitter
    • ✭✭

      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?!

      · Share on FacebookShare on Twitter
    This discussion has been closed.

    Quick Links

    Categories

    Trending Posts