#### 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.

# Range Starting point and then the rest of the row.

edited 07/12/17

Hello,

I am trying to reference an ever growing range in a COUNTIFS formula...   but it starts at about Cell 150...

I would like to have the range 150:infinity...  any Ideas?

Tags:

• ✭✭✭✭✭✭

What about doing a countif of the entire range then subtracting the countif of the range of 1-149 which would leave you with data you are looking for.

=countif([column name]:[colum name], "x"),-countif([column name]1:[column name]149, "x")

That should reserve your count to row 150 on.

• Updated...  I just realized I am using a COUNTIFS formula

• ✭✭✭✭✭✭

Could you post your countifs formula for me and I will revise mine to match.

Basically, if you can get a countifs count for the entire range using the entire column range, then you could subract the same number of rows by subtracting the same countifs formula from the first 1-149 rows. For example.

=countifs([column name]:[column name], "value", [column name2]:[column name2], "value") - countifs([column name]1:[column name]149, "value", [column name2]1:[column name2]149, "value")

• edited 07/27/17

=COUNTIFS(\$[ISSUE]\$113:\$[ISSUE]\$768, =[REPORT #]3, \$[TYPE]\$113:\$[TYPE]\$768,=\$CUSTOMER\$1, \$[DATE]\$113:\$[DATE]\$768, >=\$[DATE CLOSED]\$1, \$[DATE]\$113:\$[DATE]\$768, <=\$[DATE CLOSED]\$2)

• Also tried something like this

=COUNTIFS([ISSUE], =[REPORT #]3, [TYPE],=\$CUSTOMER\$1, [DATE], >=\$[DATE CLOSED]\$1, [DATE], <=\$[DATE CLOSED]\$2)

• Count if  this range has the same value as this particular cell,  Moving

and

Count if  this range has the same value as this particular cell, Not moving

and

Date in this range is between this date and that date....   two dates are in Cells.

• ✭✭✭✭✭✭

Hi, Jamie.

The same principle would apply. =(your workings countifs formula with no column restrictions) - (Your countifs formula with ranges from 1 to 50. )

See if this formula counts everything...

=COUNTIFS(ISSUE:ISSUE, =[REPORT #]3, TYPE:TYPE, =CUSTOMER1, DATE:DATE, >=[DATE CLOSED]1, DATE:DATE, <= [DATE CLOSED]2)

If it does, then use this one to remove rows 1-150

=COUNTIFS(ISSUE:ISSUE, =[REPORT #]3, TYPE:TYPE, =CUSTOMER1, DATE:DATE, >=[DATE CLOSED]1, DATE:DATE, <= [DATE CLOSED]2) - COUNTIFS(ISSUE1:ISSUE150, =[REPORT #]3, TYPE1:TYPE150, =CUSTOMER1, DATE1:DATE150, >=[DATE CLOSED]1, DATE1:DATE150, <= [DATE CLOSED]2)

• that did it...   thanks.

• ✭✭✭✭✭✭