Formula Error when converting to Column Formula

Kelly Mo

Any suggestions on what the problem is on the below. The formula works in a signal cell but when trying to convert to a column formula I get a syntax error.

=SUMIFS([# Of Guests Attending]:[# Of Guests Attending], [VALID DATE & TIME]:[VALID DATE & TIME], @cell = [VALID DATE & TIME]6, [Auto-Number]:[Auto-Number], @cell <= [Auto-Number]@row)

JamesB

@Kelly Mo

Smartsheets cannot create column formulas when a individual cell is referenced. i.e. [VALID DATE & TIME]6. The easiest way around this is to create a field in your sheet summary named "VALID DATE & TIME", use a formula in this field to reference =[VALID DATE & TIME]6, then in your original formula use a hashtag.

=SUMIFS([# Of Guests Attending]:[# Of Guests Attending], [VALID DATE & TIME]:[VALID DATE & TIME], @cell = [VALID DATE & TIME]#, [Auto-Number]:[Auto-Number], @cell <= [Auto-Number]@row)

Kelly Mo

I'm not very proficient can you explain a little deeper as to this suggestion?

Kelly Mo

What does this mean specifically "create a field in your sheet summary named "VALID DATE & TIME"

JamesB

@Kelly Mo

The sheet summary is an internal section of each sheet that allows you to create fields to create a sheet summary report if you will. These cannot be pulled in through a cross sheet reference formula, but they can be utilized in a internal sheet formula. On the left hand side of your sheet, is an icon for sheet summary. See screenshot below. When you create a field in this area, it is referenced in your sheet by using [field name]# in your formula argument.

Kelly Mo

Thank you however I really have no idea what you're instructing me to do. Are there step by step directions anywhere that I can leverage.

Kelly Mo

I figured out what your saying to do however that seems to tie the formula to that particular row. Would I need to create a Summary sheet for every row?

JamesB

@Kelly Mo

To better understand what you are trying to do within your formula. The formula you posted is saying perform a SUMIF of the Guest Attending, where the Date & Time column is equal to the Date&Time in row 6 and less than or equal to your Auto Number column.

=SUMIFS([# Of Guests Attending]:[# Of Guests Attending], [VALID DATE & TIME]:[VALID DATE & TIME], @cell = [VALID DATE & TIME]6, [Auto-Number]:[Auto-Number], @cell <= [Auto-Number]@row)

The question I have are you asking the formula in every row to reference Date&Time in row 6, or do you want your Formula to reference Date&Time in the row your formula lives in? (If Yes, then you will use an @row argument as shown below)

=SUMIFS([# Of Guests Attending]:[# Of Guests Attending], [VALID DATE & TIME]:[VALID DATE & TIME], = [VALID DATE & TIME]@row, [Auto-Number]:[Auto-Number], @cell <= [Auto-Number]@row)