Sheet Summary formula IF AND

LisaB:-)

Hi, I'm looking for a formula for my Sheet Summary that will count the instances of checks in a checkbox column (CD), but only if the Status column does not show Complete.

I've made a start with this, but it's #unparseable.

=COUNTIF(AND(CD:CD, "1", [Status, "Complete")

Many thanks!

MCorbin

Use Countifs when you have 2 criteria:

(and you don't need the quotes around the 1)

=COUNTIFS(CD:CD,1,Status:Status,"Complete")

Sean Morgan

Hello @LisaB:-) ,

To exclude specific items when counting in a list, we will want to use the <> operator. This operator is used for "Does not Equal" instances.

The Formula may look something like this: =COUNTIFS(CB:CB, 1, Status:Status, <>"Complete")

Furthermore, please see my example sheet below demoing this formula:

Please see this link for list of operators: https://help.smartsheet.com/learning-track/get-started/formula-basics

Let me know if you have any questions!

Regards

Sean

MCorbin

Use Countifs when you have 2 criteria:

(and you don't need the quotes around the 1)

=COUNTIFS(CD:CD,1,Status:Status,"Complete")

LisaB:-)

That's perfect, thank you!

How could I then exclude 'Completes' from the formula? So the Status could be: 'Not Started', or 'In Progress', or 'On Hold', but NOT 'Complete'?

LisaB:-)

Just bumping this up.

Sean Morgan

Hello @LisaB:-) ,

To exclude specific items when counting in a list, we will want to use the <> operator. This operator is used for "Does not Equal" instances.

The Formula may look something like this: =COUNTIFS(CB:CB, 1, Status:Status, <>"Complete")

Furthermore, please see my example sheet below demoing this formula:

Please see this link for list of operators: https://help.smartsheet.com/learning-track/get-started/formula-basics

Let me know if you have any questions!

Regards

Sean

LisaB:-)

This is really good, thank you Sean. I didn't know about <> before, and will use it much more in the future.