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!

There are instructions at the top of the page explaining how to attach your sample workbook (yellow banner: HOW TO ATTACH YOUR SAMPLE WORKBOOK). Screenshots are of little practical use as we cannot manipulate them.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

Administrative Note:

Is your forum profile showing the version of Excel that you need this to work for? "Not sure" is not acceptable.

Members will tailor the solutions they offer to the version of Office (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this.

The three most recent versions of Excel are Excel 2019, Excel 2021 and MS365 - if you are using MS365, please give this name along with the release number in your profile (e.g. MS365 Version 2211). This is in the About Excel section further down the Account page.

Thanks.

=COUNTIF(A1:A3,"A") for example does not counting #CALC!. It counts only the A's.
See attached workbook

1. Change the formula that PRODUCES the #CALC error to return a BLANK i.e. "" instead.


2. Then try:
=SUMPRODUCT(($A$10:$A$1014="Pending")*(D$10:D$1014<>""))

As suggested change your FILTER formulae to remove the #CALC:

=FILTER(OFFSET(Input!$A$1,1,XMATCH(D4,Input!1:1)-1,100,1),OFFSET(Input!$A$1,1,XMATCH(D4,Input!1:1),100,1)=$A$77,"")

and use:

=SUMPRODUCT(($A$5:$A$1009="Pending")*(D$5:D$1009<>""))

I only changed ONE instance of the filter formula... you have to do the rest.

In your case I would use the #CALC! eliminate errors. Try in C77 and copy to the right:

Formula:

Please Login or Register  to view this content.

my Excel version is Version2208!

Update your forum profile as instructed earlier. I presume this is Excel 365? Profile should read O365 v.2208.

You had #CALC, because you didn't tell The FILTER-function what the function should return if nothing found (in the 3rd parameter of FILTER).

Thanks for your feedback and glad to have helped.