Calculate count based on certain criteria.

Kevin7859

Using a helper column to either return a 1 based on certain criteria or greater than 1 based on countif. Then conditional formatting to highlight those that are >1

Current formula that works (partially):

=IF(Sit@row = "NA", 1, COUNTIFS([School Name]:[School Name], [School Name]@row, Sit:Sit, Sit@row))

Returns a 1 if the Sit is N/A but if blank or other option it returns the count >1.

I am trying to exclude the blank from the count and also if a removed checkbox is marked then subtract from the total count:

=IF(OR(Sit@row = "NA", 1, (Sit@row = "Blank", 1, COUNTIFs([School Name]:[School Name], [School Name]@row, Sit:Sit, Sit@row) - Countifs([School Name]:[School Name], [School Name]@row, Removed:Removed, Removed@row ="1")))))

Jake Gustafson

Could you share a screen shot to help visualize what you've got going?

You may need the @cell in your OR function.

Kevin7859

Like for this school, there is a Vice-chair that was removed and a new one designated, but the Check shows both so triggers the conditional formatting that highlights the one. We have another conditional format that if it is removed, then it changes the format of the cell for the Sit.

Jake Gustafson

I think you might have circular references or something. I'm still struggling to understand what you've got going on and what column/s you're looking to clean up.

Kevin7859

The formula is in the Check Sits column and checks to see if there is more than one person designated as a chair or vice chair at the school, excluding the NA. We want to exclude the Blanks as well so it doesn't count those people either. We also want to exclude those who have the removed box checked since they are no longer part of the team at the school.

Jake Gustafson

Trying to work through this.

Below will get you 1 for blank or "N/A" values and 0 for anything else. Not what you want, but gradually making progress to what you're looking for (I think).

=IF(OR(ISBLANK(Sit@row), Sit@row = "N/A"), 1, 0)

Then you have your count if the position at the school is duplicated (see below).

=COUNTIFS([School Name]:[School Name], [School Name]@row, Sit:Sit, Sit@row)

I combined those two statements to get the following:

=IF(OR(ISBLANK(Sit@row), Sit@row = "N/A"), 1, COUNTIFS([School Name]:[School Name], [School Name]@row, Sit:Sit, Sit@row))

Now you need to factor in the subtraction to each row of the same position at the same school where the Removed checkbox is selected. That's a little trickier. That's all I have time for right now. But can keep looking another time. If I'm understanding correctly from the screen shot, both those Vice Chair rows should have a 1 in the Check Sits column because the one was Removed, so you want to indicate that there is 1 Vice Chair for that school because the previous was removed from the role?

Kevin7859

Thank you all for the help on this, we ended up with the following formula:

=IF(OR(Sit@row = "NA", Sit@row = "", Removed@row = 1), 0, COUNTIFS([School Name]:[School Name], [School Name]@row, Sit:Sit, Sit@row, Removed:Removed, Removed@row = 0))

Adding the OR statement so if it was NA or <Blank> would force the 0.