COUNTIF and not blank for flags

EbonyG

Hi

I'm trying to count the number of items which are not flagged across an entire column, but don't want to include blanks in the count.

As line items are added, it will calculate if it's at risk or not at risk and add it to the count.

If the lines in the column are blank, they're ignored.

I've attached a screenshot to illustrate.

Thanks for any help you can provide.

Ebony

Jeff M.

Hi,

You could add a helper column with the formula =IF([At Risk]@row = 0, 1, 0)

Then just sum that column in your Count of Not At Risk column. =SUM([Helper]:[Helper])

Hopefully that works for you.

Jeff M.

Hi,

You could add a helper column with the formula =IF([At Risk]@row = 0, 1, 0)

Then just sum that column in your Count of Not At Risk column. =SUM([Helper]:[Helper])

Hopefully that works for you.

Andrée Starå

Hi @EbonyG

I hope you're well and safe!

To add to Jeff's excellent advice/answer.

Here's another option.

=COUNTIF([At Risk]:[At Risk], "0") - 10

Would that work/help?

I hope that helps!

Be safe and have a fantastic weekend!

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!

EbonyG

Hi @Jeff M. and @Andrée Starå

Thank you both for your answers. I really appreciate your time.

Both of them help greatly. My only query is if the formula can only count the rows which have data against them, not just having the flag unchecked.

For example, if I have rows which have an unchecked flag in them, but there is no data to the left of them, is it possible to dynamically update the formula so that if I enter data to the left of the unchecked flag, it adds it to "Not at Risk", rather than just counting the flags as a whole? I basically want to not have to update the formula each time I add a row of data.

I hope that makes sense.

If this is not possible, I'm able to work with the answers given. Should you need clarification, I am happy to provide it.

With thanks again, and kind regards,

Ebony

Andrée Starå

@EbonyG

Happy to help!

Not sure I follow! What do you mean by "data to the left of them"?

✅Please support the Community by marking the post(s) that helped or answered your question or solved your problem with the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

EbonyG

Hi @Andrée Starå

I've included a screenshot to illustrate.

Essentially, when a new line of date is added, it seems to generate a blank flag. I would like to dynamically be able to count the status of the flags (At Risk or Not At Risk) without needing to drag the formula down each time a new line is added.

I hope this explanation and screenshot help clarify what I'm after. I'm very happy to answer any questions to help get to the bottom of this.

With thanks and best regards,

Ebony

Jeff M.

I think I understand your question. A possible solution would be to use another symbol other than the flag. You could use the Red, Yellow, Green light instead. This way a new row can be added without a status symbol automatically appearing. Then by using the status type to trigger the symbol, you could then effectively count "Red" vs "Green", using the same method.

=IF(Status@row = "Green", 1)

=IF(Status@row = "Red", 1)