Using countif and offset for a named range

Hi,

I have a spreadsheet (attached) that contains id#'s, activity date, create date, datediff and employee nam.

I want to count

• the number of invoices for each employee
• the number of ids created before the activity date,
• the number of ids creaetd after the activity date,

without having to enter the range for each employee.

I am able to do this using countifs (=COUNTIF(range,"enployee name" and =COUNTIF(range,">"&0).

Since the employees and number of activities differ each time the report is run, is there a way automate this or do this with dynamic ranges, countifs and offsets?

Thanks!

You can define the range like:

MyRange: =Sheet1!$D$1:INDEX(Sheet1!$D:$D,MATCH(9.9999E+307,Sheet1!$D:$D))

that applies for the ranges with numeric entries.

MyRange2: =Sheet1!$E$1:INDEX(Sheet1!$E:$E,MATCH(REPT("z",255),Sheet1!$E:$E))

that applies for ranges with text entries

Hi Smithwa,

Welcome to the forum.

See the attached file where I have used the defined name in your formulas. Let me know in case of any questions. Thanks.


Regards,
DILIPandey

<click on below 'star' if this helps>

Hi set up two dynamic range names
Employee: =OFFSET(Sheet1!$E$1,0,0,COUNTA(Sheet1!$E:$E),1)
DateDiff: =OFFSET(Sheet1!$D$1,0,0,COUNTA(Sheet1!$E:$E),1)

Now in
H2: =COUNTIF(Employee,G2)
I2: =COUNTIFS(DateDiff,">="&0,Employee,G2)
J2: =COUNTIFS(DateDiff,"<"&0,Employee,G2)

and copy H2:J2 down

Regards