Total rows in specific column excluding extra column if checked

Beth Faircloth

All,

(Please see screenshot for reference)

I am trying to get a "total locations" excluding if "cancelled" is checked. Current formula in "total locations is.

=COUNT([Location #]:[Location #])

This gives the total of every row in the "Location #" column, but I need to show the total of what is left if one is marked "cancelled". Can anyone help?

Looking for something similar in the below screenshot. Basically I need to exclude "cancelled" in each of these.

"month" only counts locations if "cancelled" is not checked. Same for the part #'s.

Genevieve P.

Hi Beth,

1 . Try this for your first picture:

=COUNTIFS([Location #]:[Location #], <> "", Cancelled:Cancelled, <> 1)

This will check the Location # column to make sure it's not blank (represented as ""), and then count all those rows as long as the Cancelled column does not have a check (represented as 1). The <> symbol represents "not equal to."

2 . For your second image, I presume you have a date column type not shown, is that correct? If so, for each of the months you can use the same COUNTIFS formula, but specifying the Month:

=COUNTIFS([Date Column]:[Date Column], MONTH(@cell) = 1, Cancelled:Cancelled, <> 1)

Here the MONTH(@cell) = 1 is looking for the first month in the year , January. You can change the number for each formula, looking for the different months (see here).

3 . Finally, if you're looking for a specific count based on the Part number, try the following:

=COUNTIFS([Part Number]:[Part Number], = "108-020", Cancelled:Cancelled, <> 1)

Keep in mind that you will need to update the column references if they are titled differently than my examples here. Here are some Help Center articles I used for this: COUNTIFS function / Formulas and Functions

Let me know if these work for you!

Cheers,

Genevieve

Genevieve P.

Hi Beth,

1 . Try this for your first picture:

=COUNTIFS([Location #]:[Location #], <> "", Cancelled:Cancelled, <> 1)

This will check the Location # column to make sure it's not blank (represented as ""), and then count all those rows as long as the Cancelled column does not have a check (represented as 1). The <> symbol represents "not equal to."

2 . For your second image, I presume you have a date column type not shown, is that correct? If so, for each of the months you can use the same COUNTIFS formula, but specifying the Month:

=COUNTIFS([Date Column]:[Date Column], MONTH(@cell) = 1, Cancelled:Cancelled, <> 1)

Here the MONTH(@cell) = 1 is looking for the first month in the year , January. You can change the number for each formula, looking for the different months (see here).

3 . Finally, if you're looking for a specific count based on the Part number, try the following:

=COUNTIFS([Part Number]:[Part Number], = "108-020", Cancelled:Cancelled, <> 1)

Keep in mind that you will need to update the column references if they are titled differently than my examples here. Here are some Help Center articles I used for this: COUNTIFS function / Formulas and Functions

Let me know if these work for you!

Cheers,

Genevieve

Beth Faircloth

Thanks Genevieve. The first one worked. The other two suggestions gave an error. I'm including screenshots of the exact headings I have. This might help.

Genevieve P.

Hi Beth,

Yes, you will need to update the column names. Try this for your second one:

=COUNTIFS([Date Scheduled]:[Date Scheduled], MONTH(@cell) = 1, Cancelled:Cancelled, <> 1)

Make sure that when you're referencing a column the name in between [these brackets] lights up in a specific colour (such as pink, purple, etc). This will indicate that the formula has found the column you are looking to reference.

For your third one, I'm not quite sure I understand where your "Part Number" column is. Are all of those different columns Part Numbers that you want totalled? If so, you would just need to adjust the first formula to have the Column Name in the first range (bolded below):

=COUNTIFS([108-020]:[108-020], <> "", Cancelled:Cancelled, <> 1)

Hope that helps!

Genevieve

Beth Faircloth

Those worked perfect! Thanks so much for the help.

Genevieve P.

Wonderful! Glad to have helped.