COUNTIFS for multiple values in a column, as well as individual values from other columns.

Joy Felter

I'm trying to write a formula that counts the number if items John has deployed in a sprint with a Work Type of Configuration Change, Quick Win or Stat Change. I'm getting the data from a separate sheet, and the column names are Primary Assignee, Sprint, Status and Work Type.

I tried

=COUNTIFS({Primary Assignee}, $John$1, {Status}, $Sprint$1, {Sprint}, Sprint@row, {Work Type}, [Team Total]35, {Work Type}, [Team Total]36)

but I'm getting 0, I think the 2 work type values are never in the same cell.

I also tried

=COUNTIFS({Primary Assignee}, $John$1, {Status}, $Sprint$1, {Sprint}, Sprint@row, +COUNTIFS({Work Type}, [Team Total]35), +COUNTIFS({Work Type}, [Team Total]36))

but now I'm getting #INCORRECT ARGUMENT and I can't figure it out. Any help will be greatly appreciated!

AdamSYNH

Hi @Joy Felter,

You'll need a COUNTIFS for each of your Work Type options, then you can just add them together. I can see that this is what you're trying to do in the formulas you've pasted. The below should work assuming [Team Total]35, [Team Total]36 etc. refer to the cells which contain the work types you listed.

=COUNTIFS({Primary Assignee}, $John$1, {Status}, $Sprint$1, {Sprint}, Sprint@row, {Work Type}, [Team Total]35) + COUNTIFS({Primary Assignee}, $John$1, {Status}, $Sprint$1, {Sprint}, Sprint@row, {Work Type}, [Team Total]36)