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

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.

Best Answer

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    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

    Join us for Jumpstart 2025 with Community on 23 January (in two time zones)! 🎉 Register here.

Answers

  • =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.
    Genevieve P. Employee
    Answer ✓

    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

    Join us for Jumpstart 2025 with Community on 23 January (in two time zones)! 🎉 Register here.

  • 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.

    Join us for Jumpstart 2025 with Community on 23 January (in two time zones)! 🎉 Register here.

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


  • 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?

    Join us for Jumpstart 2025 with Community on 23 January (in two time zones)! 🎉 Register here.

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

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

    Join us for Jumpstart 2025 with Community on 23 January (in two time zones)! 🎉 Register here.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!