Jun 24 2020 03:28 PM - edited Jun 24 2020 03:37 PM
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.
Jun 24 2020 03:58 PM
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
Jun 24 2020 06:13 PM
SolutionJun 25 2020 07:29 AM
@Twifoo Thank you, thank you, thank you!!! That worked beautifully!!
Jun 25 2020 11:40 AM
Jun 25 2020 01:06 PM
If you have the newest version of Excel, you might be able to use a construct along the following lines.
=COUNTA(FILTER(range, criteria))
I could create an example if you're able to upload a sample (without real names) of your database.
Jun 25 2020 01:11 PM
Jun 25 2020 01:53 PM
Two ways. If codes in T, W and Z columns are numbers, when as @Twifoo suggested. If codes are texts when
=COUNTIFS(T2:T10,CHAR(173) & "300",
W2:W10,CHAR(173) & "300",
Z2:Z10,CHAR(173) & "300")
or
=SUMPRODUCT(
(T2:T10 = "300")*
(W2:W10 = "300")*
(Z2:Z10 = "300")
)
COUNIF(S) converts criteria parameter defined as texts into number where possible, adding of soft hyphen is workaround. But SUMPRODUCT is more reliable from my point of view.
Jun 24 2020 06:13 PM
Solution