Sheet Summary Formula > COUNTIF for column using formula

Dana G

Hello, I maintain a large document tracker. It has one "Pending" column that uses a massive formula (pasted below for reference) to search the tracker for missing information, and if it identifies any, the column is flagged. I'd like to create a summary formula to count the number of pending documents. I'm trying =COUNTIF(Pending:Pending, 1) but it keeps returning #INVALID OPERATION. Do you know, is my crazy formula the issue, or something else? Thanks for your help!

Massive formula:

=IF(OR(CONTAINS("TBD", Lead@row), CONTAINS("TBD", [Due Date]@row), CONTAINS("TBD", [Submission Date]@row), CONTAINS("TBD", [Redact Status]@row), CONTAINS("TBD", [Other Information]@row), CONTAINS("TBD", [Regulatory Team Notes]@row), Escalation@row = 1, [Escalation Notes]@row <> "", OR([Submission Status]@row = "Submitted, pending Follow-up", [Submission Status]@row = "Pending Request", [Submission Status]@row = "#INVALID OPERATION"), AND(OR([Submission Status]@row = "Submitted", [Submission Status]@row = "Submitted, pending Follow-up"), OR([Redact Review]@row = "Yes", [Redact Review]@row = "Yes"), [Redact Submission Date]@row = "", [Redact Status]@row = ""), AND(OR([Submission Status]@row = "Submitted?", [Submission Status]@row = "Requested?"), [Due Date]@row < TODAY()), [Copy of Submission]@row = "Pending upload"), 1)

Kelly Moore

Hey @Dana G

Your summary formula field should be formatted as a Text/Number field. Please verify.

Dana G

Hi @Kelly Moore, Thanks for your help. I've confirmed the summary field is a Text/Number field.

Kelly Moore

Hey @Dana G

Let's use the COUNTIFS (remember you can always use COUNTIFS even if only one criteria is listed)

=COUNTIFS(Pending:Pending, ISNUMBER(@cell), Pending:Pending,1)

Are there any error messages in the Pending column?

Dana G

Hi @Kelly Moore, that was it! I had a few error messages in the Pending column. Once I cleared those up, the simpler =COUNTIF(Pending:Pending, 1) formula works! Thanks again for your expertise :)

Kelly Moore

Glad we got it to work. Just for my reference - did you try the 'longer' formula before you cleared the errors? I had hoped the ISNUMBER would force the cells with errors to be ignored. It doesn't always work...

Dana G

@Kelly Moore, I did try the longer formula and received the #INVALID OPERATION error.