Formula error when filtering an unchecked flag column & filled cell (in column)

Char Norman

Hi,

I've been trying to complete a formula that counts the number of unchecked flags when a cell states approved.

Here is my current formula that responds with UNPARS

=COUNTIF([Pause New Grp Reg Reminder]:[Pause New Grp Reg Reminder], OR(1, 0), AND([Facilitator Status - Certificate Completed]@row:[Facilitator Status - Certificate Completed]@row, 1, 0))

Here is a screen shot of the columns

I know my this portion of my formula correctly counts the unchecked flags: =COUNTIF([Pause New Grp Reg Reminder]:[Pause New Grp Reg Reminder], OR(1, 0)

But I then only want to count the Facilitators that have been Approved.

Thanks for you insights!

Char

Char Norman

As a second note, I need to understand why my formula isn't working for Approved facilitators with flags.

So I need Approved facilitators with flags; and Approved facilitators without flags.

Thanks again!

Paul Newcome

Try this:

=COUNTIFS([Pause New Grp Reg Reminder]:[Pause New Grp Reg Reminder], @cell <> 1, [Facilitator Status - Certificate Completed]:[Facilitator Status - Certificate Completed], @cell = "Approved")

Char Norman

Works wonderfully. Thank you.

I'm now trying to figure out the formula where the the Facilitator Status row is blank. I tried "" but the counts are not correct.

Paul Newcome

Are they consistently ten more than what it should be?

Char Norman

No. For some reason the count is off.

There are 49 applicants accepted.

There are 25 applicants accepted & are now approved facilitators.

Therefore there should be 24 non-approved facilitators (not 25).

Here's my formula: =COUNTIF([Facilitator Status - Certificate Completed]:[Facilitator Status - Certificate Completed], <>"")

Paul Newcome

Your formula is counting rows where the cells within the range are not blank.

Char Norman

I have tried this formula and still can't get the count correct. Based on my filter (which is correct at 29 [59-30]), I'm not sure what is wrong with my formula. I have tried it several ways and even used University to see what others have done. Help!!

=COUNTIF([Facilitator Status - Certificate Completed]:[Facilitator Status - Certificate Completed], ISBLANK(@cell))

Paul Newcome

What is the count you are e getting with the new COUNTIF, and what exactly are your filter settings?

Char Norman

The count I'm getting with the COUNTIF is 43 ???

The filter settings

Paul Newcome

What happens if you delete the rows below your last row of data? Not just clearing out the cells in those rows but using the row menu to delete the rows?