Connect with peers, share your expertise, and inspire what’s next in Smartsheet — from proven practices to practical how-to insights from fellow users and product experts.
Sign in to join the conversation:
The posts in this section are historical and no longer monitored for accuracy. If this discussion interests you and you'd like to join in, please visit the active Community to post and engage.
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?
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")
=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
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.
Awesome. Glad I could help.
