need a formula to return the number of forms filled out by a person(x) with a score of x

QA_Training_Agent3

I need a formula that will return the total number of forms filled out by a specific person with a score of 5. It is cross sheet references and I am stuck. I have found some saying vlookup or countif. Butt neither formulas are working.

Genevieve P.

Hi @QA_Training_Agent3

If you have multiple criteria that you're looking for (a person and a minimum score), then you'll want to use COUNTFS, plural! In this instance you won't need to say IF or AND again within the function, because it's already plural.

A COUNTIFS Function works like this:

=COUNTIFS({Column 1}, "Criteria 1", {Column 2}, "Criteria 2")

So in your case, try this:

=COUNTIFS({QA Team Weekly Reflection Sheet Range 7}, "Rose Kakuk", {QA Team Weekly Reflection Sheet Range 4}, 5)

If you're looking for any score greater than 4, then you can adjust the criteria to this:

=COUNTIFS({QA Team Weekly Reflection Sheet Range 7}, "Rose Kakuk", {QA Team Weekly Reflection Sheet Range 4}, @cell > 4)

Let me know if this works for you!

Cheers,

Genevieve

QA_Training_Agent3

=COUNTIF({QA Team Weekly Reflection Sheet Range 7},"Rose Kakuk", and If({QA Team Weekly Reflection Sheet Range 4},<4))

here is one that I tried

Genevieve P.

Hi @QA_Training_Agent3

If you have multiple criteria that you're looking for (a person and a minimum score), then you'll want to use COUNTFS, plural! In this instance you won't need to say IF or AND again within the function, because it's already plural.

A COUNTIFS Function works like this:

=COUNTIFS({Column 1}, "Criteria 1", {Column 2}, "Criteria 2")

So in your case, try this:

=COUNTIFS({QA Team Weekly Reflection Sheet Range 7}, "Rose Kakuk", {QA Team Weekly Reflection Sheet Range 4}, 5)

If you're looking for any score greater than 4, then you can adjust the criteria to this:

=COUNTIFS({QA Team Weekly Reflection Sheet Range 7}, "Rose Kakuk", {QA Team Weekly Reflection Sheet Range 4}, @cell > 4)

Let me know if this works for you!

Cheers,

Genevieve

QA_Training_Agent3

it returns unparseable.

Genevieve P.

Hi @QA_Training_Agent3

Try removing those two pink parentheses. You can go right from "Rose Kakuk",  to the next {reference}.

You may also want to check to ensure that each of your Ranges is referencing the correct column.

QA_Training_Agent3

Do I have the order wrong? I am I clicking in the wrong columns? this is the sheet I am referencing.

Genevieve P.

Hi @QA_Training_Agent3

If you're looking for the number 5 in the Morale column, then I assume these are the ranges you selected, is that correct?

{QA Team Weekly Reflection Sheet Range 7} = Name Column

{QA Team Weekly Reflection Sheet Range 4} = Morale Column

If so, this should be the structure of your formula:

=COUNTIFS({Name Column}, "Rose Kakuk", {Morale Column}, 5)

I also see that the Morale column is a multi-select column. Is there a possibility someone may select 5 along with another number?

QA_Training_Agent3

Well this is strange. I deleted everything and started over and now they are working. thank you.

Genevieve P.

No problem! I'm glad you got it working.