Range Starting point and then the rest of the row.

Jamie Bombardier

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?

Mike Wilday

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. 

Jamie Bombardier

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

Mike Wilday

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

Jamie Bombardier

=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)

Jamie Bombardier

Also tried something like this

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

Jamie Bombardier

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.

 

Mike Wilday

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) 

Jamie Bombardier

that did it...   thanks.

Mike Wilday

Awesome. Glad I could help.