Count column that is blank and another column that is not blank

lesmickin

I am trying to do a formula to count how many files are not yet audited:

where my "closed/referred date" column is not blank, but the "audited" column is blank.

Ive looked at other examples, but keep getting unpareseable errors any help would be greatly appreciated

Genevieve P.

Hi @lesmickin

It looks like your "Audited" column is a Checkbox type of column, is that correct? If so, instead of looking for "" (which looks for blank) you'll actually want to look for an un-checked box, or 0.

Try this:

=COUNTIFS([Closed/Referred Date]:[Closed/Referred Date], <>"", Audited:Audited, 0)

Cheers,

Genevieve

Andrée Starå

Hi @lesmickin

I hope you're well and safe!

Try something like this.

=COUNTIFS([Closed/referred date]:[Closed/referred date], <>"", Audited:Audited, "")

Did that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

lesmickin

Good morning Andree,

It actually gives me a result of 0. I placed the formula in both the smartsheet (as a separate column) and in the sheet summary section. all with the same results :(

Andrée Starå

@lesmickin

Strange!

I'd be happy to take a quick look.

Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@workbold.com)

lesmickin

Thank you for looking into this for me. I am unable to send to outside entitites due to our security levels, so i've attached it here, but, what I am trying to accomplish is you will see I have rows that are marked as closed/referred (date entered in the closed/referred date field) and some have been audited while others have not. I am trying to get the number of those that have not been audited but are closed.

Template of CRU.xlsx

Genevieve P.

Hi @lesmickin

It looks like your "Audited" column is a Checkbox type of column, is that correct? If so, instead of looking for "" (which looks for blank) you'll actually want to look for an un-checked box, or 0.

Try this:

=COUNTIFS([Closed/Referred Date]:[Closed/Referred Date], <>"", Audited:Audited, 0)

Cheers,

Genevieve

lesmickin

thank you Genevieve, it worked perfectly!

Genevieve P.

Wonderful! I'm glad Andrée and I could help! 🙂

Andrée Starå

@lesmickin

Excellent!

Glad we got it working!