How can I exclude counting ?#CALC!? when using the function COUNTIF()?

Hi, I?m experiencing an issue with the function COUNTIF(). The value in cell D7 is supposed to be 0, but Excel counts ?#CALC!? as a valid value and instead returns 1. I wrote a formula to count the ?Pending? cases if a row in the column ?Anmed? is not blank. Is my approach incorrect to achieve what I?m trying to do?

=COUNTIFS($A$10:$A$1014, "Pending", D$10:D$1014, "<>"&"", D$10:D$1014, "<>"&"#CALC!")

Capture.PNG





Thank you for your help!