CountIfs Question

Options

I am trying to use the countifs formula to fill in my request metrics sheet. I need to count how many times one person has been assinged specific items from a drop down.

=COUNTIFS(Category47, {INNOV_Request Tracker Range 1}, [Request Status]:[Request Status], "Analysis Approved", "Project Approved", "Task Approved", "Under Review", 1)

This is the formula that I am using and it keeps giving me unparseable as my answer.

The drop down options are in the request status column and I need to count 4 of them.

Help!

Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    edited 02/10/22 Answer ✓
    Options

    Hi @DeMetria Hines

    There are two things to note here.

    1) First, it looks like you're searching for the person identified in the cell to the left (ex. "DeMetria Hines"), but you don't list the column to look for that value.

    Find the column with the Names in a {Cross Sheet Reference}, like so:

    =COUNTIFS({Column with Names}, Category@row,

    Then once you have your first column and criteria listed, you can add the second column and criteria:

    =COUNTIFS({Column with Names}, Category@row, {Column with Request Status}, "Analysis Approved")

    This should give you a COUNT of the number of rows that the person on the left has in the "Analysis Approved" Status column.


    2) Secondly, to count other Statuses as well, you'll just need to re-do the formula and add all your values together with +, like so:

    =COUNTIFS({Column with Names}, Category@row, {Column with Request Status}, "Analysis Approved") + COUNTIFS({Column with Names}, Category@row, {Column with Request Status}, "Project Approved") + COUNTIFS({Column with Names}, Category@row, {Column with Request Status}, "Task Approved") + COUNTIFS({Column with Names}, Category@row, {Column with Request Status}, "Under Review")

    Cheers,

    Genevieve

Answers

  • hollyconradsmith
    hollyconradsmith ✭✭✭✭
    Options

    Hi @DeMetria Hines,

    You need to identify one criterion per pair, so where you have "Analysis Approved", "Project Approved", "Task Approved", "Under Review" all listed for the [Request Status]:[Request Status], it is unable to figure out what you are asking for. You might be better off using the <> (not equal to) to filter for anything that is not your unassigned category.

    [Request Status]:[Request Status], <>"Assignment Pending"

    Holly Conrad Smith

    Director of Technology & Innovation 💡 at Streamline

    CliftonStrengths Top 5: Deliberative, Restorative, Achiever, Consistency, Harmony

  • Genevieve P.
    Genevieve P. Employee Admin
    edited 02/10/22 Answer ✓
    Options

    Hi @DeMetria Hines

    There are two things to note here.

    1) First, it looks like you're searching for the person identified in the cell to the left (ex. "DeMetria Hines"), but you don't list the column to look for that value.

    Find the column with the Names in a {Cross Sheet Reference}, like so:

    =COUNTIFS({Column with Names}, Category@row,

    Then once you have your first column and criteria listed, you can add the second column and criteria:

    =COUNTIFS({Column with Names}, Category@row, {Column with Request Status}, "Analysis Approved")

    This should give you a COUNT of the number of rows that the person on the left has in the "Analysis Approved" Status column.


    2) Secondly, to count other Statuses as well, you'll just need to re-do the formula and add all your values together with +, like so:

    =COUNTIFS({Column with Names}, Category@row, {Column with Request Status}, "Analysis Approved") + COUNTIFS({Column with Names}, Category@row, {Column with Request Status}, "Project Approved") + COUNTIFS({Column with Names}, Category@row, {Column with Request Status}, "Task Approved") + COUNTIFS({Column with Names}, Category@row, {Column with Request Status}, "Under Review")

    Cheers,

    Genevieve

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!