Vrftjkry

I am having an issue.

My formula works and comes up with #VALUE when it has no data ready for it to calculate. However, when data is put in for it to calculate it won't pick it up unless I delete the formula and retype it in. I don't change the formula at all but it suddenly allows for itself to work.

Here is the formula:

=COUNTIFS('Data Input - All Students'!D$37:D$639,"*Writing*",'Data Input - All Students'!N$37:N$594,"PD 1 (i)")

Things I've tried/checked

• File - Options - Formulas - Workbook Calculations - Automatic




• Deleting and retyping formula (works.. but document is too big with too many formulas for this to be practical)

The article at support.office.com for the COUNTIFS function states:

Important: Each additional range must have the same number of rows and columns as the criteria_range1 argument. The ranges do not have to be adjacent to each other.<

In your formula critera_range1 is D$37:D$639 while your criteria_range2 is N$37:N$594. Change criteria_range2 to N$37:N$639 and the formula should work.

You're not telling us the whole story, or at least not accurately. 
COUNTIFS counts the number of cells in criteria_range1
where criteria1 is true,
and where criteria2 is true for the corresponding cell in criteria_range2,
and so on (up to 127 range/criteria pairs). 
In order for the concept "the corresponding cell" to be meaningful / valid,
all the ranges must be the same dimensionality. 
This is stated in the documentation for COUNTIFS:

Important:
Each additional range must have the same number of rows and columns
as the criteria_range1 argument.

here and here.

You are using COUNTIFS on two ranges that are not the same size:

• 
  D$37:D$639 is 603 rows, and


• 
  N$37:N$594 is 558 rows.

That formula will never work.

My guess is that you have some other version of that formula,
where the two ranges are the same size, and when you "retype" the formula,
you are actually typing in the correct version.

P.S. I've been staring at this for the past half hour,
and I just noticed that the two ranges start in the same row
and end in different rows. 
So of course they are different numbers of rows long.

I can't help you cure the cause, but would it work and make it more bearable in the short run (until you get a better answer) if you press F9 to "Calculate Now"? If the document is massive I suspect it might be the cause, but there are a lot of things to consider.