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.
