Tracking Complete/In Process/Late Assignments Using COUNTIFS or Pivot Table

[exceltracker02]

I'm working with a large data set with three relevant columns labeled "Case Status", "Lead Team Staff", and "Lead Team Due Date." I'm trying to prepare a four column table as follows:

A.Staff Name B.Completed Assign. C. In-Process Assign. D. Late Assign.


My first thought was to create a pivot table. But I ran into a problem with the "Late Assignments" column because I couldn't dynamically filter the "Lead Team Due Date"<today(). (apparently,="" pivot="" tables="" do="" not="" like="" dynamic="" formulas.)

My next approach was to use COUNTIFS. I succeeded in making it work with a fixed range, but when I tried to create two dynamic named ranges, the results returned a #VALUE error. A search of this board pointed me here, which I believe is the problem I'm encountering too...i.e. the dynamic ranges are different sizes because of blanks in one of them.

So my question is if there's a way to create two dynamic ranges of the same size. I want one range based on column D, and one based on column AY. And what I'm hoping is that if I could have the second range include blank cells, then they would be the same size and COUNTIFS would filter out the blanks in the final chart. In other words, both dynamic ranges should have the same number of rows, but in one of the ranges many of the column entries will be blank.

Does this make sense? I can upload a dummy file if necessary. Thanks in advance for any suggestions.</today().>

 

[exceltracker02]

Sorry, I realize I forgot some of the basics. I'm using Excel 2007. My COUNTIFS formula looks like this:

=COUNTIFS(Case_Status,"Completed*",L_Team_Staff,"Last Name, First Name")

Where "Case_Status" and "L_Team_Staff" are my dynamic named ranges. I created them using the formulas:

=OFFSET(Sheet1!$D$2,0,0,COUNTA(Sheet1!$D:$D),1)

=OFFSET(Sheet1!$AY$2,0,0,COUNTA(Sheet1!$AY:$AY),1)

As mentioned in my original post, every cell in the "Case_Status" range has an entry, but many in the "L_Team_Staff" do not. So is there a way to make the dynamic ranges the same size, and then have the COUNTIFS function do my blank filtering?