Counting only unique value with multiple submissions

KevinMio8

I am not quite sure how to get a formula to work with the following exercise: I use a form to collect data from reviewers, they may make multiple entries in order to answer all of the questions, so 1 row may have study ABC withan answer of 'Critical' for questions 1 & 3, later they will submit ABC and answer 'critical' for question 2 & 5. This will be the case for the other study numbers as well.

I set up a 'Unique Study Number' column to get one instance of each study number and I am trying to get a formula to count all instances of critical, major and minor for each of the unique studies. Any ideas would be appreciated. See snippit of sheet below:

Paul Newcome

All ranges within the same function must be of the same size and shape. You can't have one range that looks at 5 columns and then another range that only looks at 1 column. You will need separate COUNTIFS (one for each of the risk columns) all added together.

=COUNTIFS([Risk1]:[Risk1], @cell = "Critical", [Study Number]:[Study Number], @cell = [Unique Study Number]@row) + COUNTIFS([Risk2]:[Risk 2], @cell = "Critical", [Study Number]:[Study Number], @cell = [Unique Study Number]@row) + COUNTIFS(…………………………..

Michelle Choate 2

Hello @KevinMio8 !!

This is a great COUNTIFS formula for each column you have highlighted.

For Critical: =COUNTIFS(Risk1:Risk5, "Critical", [Study Number]:[Study Number], [Unique Study Number]@row)

For Major: =COUNTIFS(Risk1:Risk5, "Major", [Study Number]:[Study Number], [Unique Study Number]@row)

For Minor: =COUNTIFS(Risk1:Risk5, "Minor", [Study Number]:[Study Number], [Unique Study Number]@row)

Hope this helps!

KevinMio8

I believe I tried the formula you provided before I submitted the question and it it did not work, so I copy and pasted your formula in the appropriate cell it did not work - I got a 'unparsable' error, when I added the square brackets around the [risk1]:[risk5] and tried again it resulted in an 'Incorrect argument set' error.

Paul Newcome

All ranges within the same function must be of the same size and shape. You can't have one range that looks at 5 columns and then another range that only looks at 1 column. You will need separate COUNTIFS (one for each of the risk columns) all added together.

=COUNTIFS([Risk1]:[Risk1], @cell = "Critical", [Study Number]:[Study Number], @cell = [Unique Study Number]@row) + COUNTIFS([Risk2]:[Risk 2], @cell = "Critical", [Study Number]:[Study Number], @cell = [Unique Study Number]@row) + COUNTIFS(…………………………..

KevinMio8

That solved it - thanks. I thought I was close with the countifs for the study number part and a + or( with all of the risk columns but not quite right.

Paul Newcome

Happy to help. 👍️