Countifs error

Copper Contributor



I have a running spreadsheet that is supposed to determine the number of assignments for each day, for each staff member.  When I first created the sheet, I only set it for 2999 rows of data, but I have now exceeded that.  When I tried to revise the formula, I receive a #Value error.  The error is in the Assignments part of this formula.  I see no differences between row 2999 and 3000, but even if I change it to 3000 I get an error. 

Here is the formula:

=COUNTIFS(DStaff,A4,Assignments!$A$2:$A$2999,'DRU Stats'!$AB$2)


DStaff is the list of all my staff

A4 is one of the names of my staff

Assignments A2:  is the date I have assigned the work

DRU stats AB2 is =Today()


Any help would be appreciated!!


3 Replies


Are DStaff and Assignments!$A$2:$A$3000 have the same size?

DStaff is a list of 3 people. I use a drop-down to populate the field. I have over 3000 assignments and growing rapidly. This is just to calculate how many each person receives per day.


I should add that it worked perfectly until I reached 2999.


It could be 3 values in it, but what is the size of the range with that name?