Need help with a COUNTIFS formula

[dpeck]

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.

[#2]

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

[#3 · Accepted solution]

Try this formula:
=SUM(COUNTIFS(A2:A57,”X”,
P2:P57,{“SUS”,”IH”}))

[#4]

@Twifoo  Thank you, thank you, thank you!!!  That worked beautifully!!

[#5]

You’re welcome!

[#6]

Now, how about if I need to count Incident codes from 3 different columns?

I tried to tweak the previous one, but it didn't work. What I originally had was:
=COUNTIFS(T2:T10,"300",W2:W10,"300",Z2:Z10,"300") - that brought up 0.

I tried =SUM(COUNTIFS({T2:T10,W2:W10,Z2:Z10},"300")) but that caused an error.

I tried =SUM(COUNTIFS(T2:T10,W2:W10,Z2:Z10,{"300"})) - that brought up 0

So basically, I need a total of all the 300 codes from column T, W & Z. Is that possible?

[#7]

@dpeck 

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.

[#8]

It seems 300 in your criteria must be a number. If so, remove the quotes around it.

[#9]

@dpeck 

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.