Reference Entire Column in Function

Justin Hayes

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.

Find more posts tagged with

Formulas

Comments

J. Craig Williams

Looks OK to me.

What's the problem?

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

Craig

Aaron Torres

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)

Aaron Torres

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

admin58441

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

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

joecross98

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