Forum Discussion
Need help with a COUNTIFS formula
I'm working on a spreadsheet for state reporting (I work for a school district) and I need to get the unduplicated number of students who received a suspension or in-house suspension.
I've marked the unduplicated students with an x in column A and column P has the SUS and IH codes. I can't figure out how to get OR to work.
This is what I have:
=COUNTIFS(A2:A57,"X",P2:P57,"SUS",P2:P57,"IH") and it brings up 0 students because it's looking for students marked x with both SUS & IH. I have 34 unduplicated students. The results of the formula should be the same, so somehow I need to stick OR in there & I can't figure out how to get it to work.
It's driving me slightly batty.
- Try this formula:
=SUM(COUNTIFS(A2:A57,”X”,
P2:P57,{“SUS”,”IH”}))
8 Replies
- Try this formula:
=SUM(COUNTIFS(A2:A57,”X”,
P2:P57,{“SUS”,”IH”})) Hi dpeck,
this is a basic or function: =OR(B1="x",C1="x")
Which will return "true" if either or both cells are marked with an "x". otherwise, it returns false.
Alternatively you could use: =XOR(B1="x",C1="x")
Which returns true only if one or the other cells is marked with an "x" but if both or none are marked, then it returns false.
So adding this in your spreadsheet, in say column "Z", you could simply count the number of true or false.
Ben
