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
-
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 at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
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
-
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 at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
it returns unparseable.
-
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 at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
Do I have the order wrong? I am I clicking in the wrong columns? this is the sheet I am referencing.
-
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 at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
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 at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.3K Get Help
- 364 Global Discussions
- 199 Industry Talk
- 428 Announcements
- 4.4K Ideas & Feature Requests
- 136 Brandfolder
- 127 Just for fun
- 128 Community Job Board
- 445 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!