Formulas not calculating correctly

[EvelynTN]

Help! I have spreadsheet in excel, and a few of the cells are not calculating correctly. My worksheet has 4,000+ rows, and one column has the following formula:

=IF(AL4051="D",IF(+T4051=1,COUNTIFS(AL$2:AL4051,"D",U$2:U4051,"<3",A$2:A4051,A4051,T$2:T4051,1),0),0)

The formula calculated correctly for all but 6 of the cells. For these cells, it tripped up in the column U value for the row. The 6 cells had "3" as the value in column U (and all other conditions to be counted met), and it returned a "1" instead of a "0".

I read about 'dirty cells'. I fixed three of the formulas by replacing the equals sign (click and dragging the formula didn't work). Same formula, but apparently triggered it to recalculate. Then I saved the worksheet. I went to fix the remaining three formulas, and replacing the equals sign didn't work on them.

Even if I can fix these remaining 3 cells, there is a count on another sheet that isn't updating after the 3 fixed cells were changed. Help!

Is there a way to globally fix this dirty cell problem? I really don't want the manually check every time the worksheet is updated. i am using Excel 2016.

Thanks in advance, Evelyn

 

[EvelynTN]

yes, I fixed it after I posted the Dropbox. Thank you!

 

[DanteAmor]

Youre welcome, thanks for the feedback.

 

[joeu2004]

=IF(AL3936="D",IF(T3936=1,IF(U3936<3,COUNTIFS(AL$2:AL3936,"D",A$2:A3936,A3936,T$2:T3936,1),0),0),0)

Arguably, a simplification:

=IF(AND(AL3936="D",T3936=1,U3936<3),COUNTIFS(AL$2:AL3936,"D",A$2:A3936,A3936,T$2:T3936,1),0)

When I am building aformula from inside out, I'll start with +before the cell address.[/FONT][/COLOR]

I don't know why you would. It's not like you're using the numeric keypad for such formulas. (The excuse that some people give for starting formulas with "+".)

Whatever! I just wanted to be sure there was no misunderstanding.