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.

Tags:

Comments

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭

    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.

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭

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

This discussion has been closed.