Forum Discussion

dpeck's avatar
dpeck
Copper Contributor
Jun 24, 2020
Solved

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...
Formulas and Functions
  • Twifoo's avatar
    Twifoo
    Jun 25, 2020
    Try this formula:
    =SUM(COUNTIFS(A2:A57,”X”,
    P2:P57,{“SUS”,”IH”}))
dpeck's avatar
dpeck
Copper Contributor
Jun 25, 2020

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

Twifoo's avatar
Twifoo
Silver Contributor
Jun 25, 2020
You’re welcome!
  • dpeck's avatar
    dpeck
    Copper Contributor
    Jun 25, 2020
    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?
    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Jun 25, 2020

      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.

    • Twifoo's avatar
      Twifoo
      Silver Contributor
      Jun 25, 2020
      It seems 300 in your criteria must be a number. If so, remove the quotes around it.
    • mathetes's avatar
      mathetes
      Silver Contributor
      Jun 25, 2020

      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.

Resources