Countifs - Multiple Criteria - Same sheet but two different columns.

I'm trying to use the COUNTIFS to look in a range for a name and only give me those with the quarter of Q1 2021. When I try to combine I should see 4, but the formula is giving me 24.


=COUNTIFS({2021 X Projects - Marketing Range 3}, "Todd") + COUNTIFS({2021 X Projects - Marketing Range 2}, "Q1 2021")

Answers

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi @Michael Thigpen

    I hope you're well and safe!

    Can you maybe share some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@workbold.com)

    I hope that helps!

    Be safe and have a fantastic week!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Michael Thigpen

    Currently you have two separate formulas: one counts how many times "Todd" is in a column, the other counts how many times "Q1 2021" is in the other column, then it adds these two numbers together. This means that if "Todd" is with "Q1 2021" you get a Count of 2. If "Todd" has a different quarter, you get a count of 1, since your first Count finds one match.

    Are you looking to only count where these two criteria are on the same row? (Ex. both "Todd" and "Q1 2021")

    If so, add these criteria into the same COUNTIFS function, like so:


    =COUNTIFS({2021 X Projects - Marketing Range 3}, "Todd", {2021 X Projects - Marketing Range 2}, "Q1 2021")


    Let me know if this works for you!

    Cheers,

    Genevieve

  • Yes, I'm looking to count based on those two criteria. I will try your formula.

  • Hi Genevieve!

    I tried the formula: =COUNTIFS({2021 X Projects - Marketing Range 1}, Todd",{2021 X Projects - Marketing Range 2}, "Q1 2021")

    This comes back as #UNPARSEABLE

    If I try with only one part of the formula like Todd or Q1 2021 it will give me the formula. But when they are together, I get the above error.

    Same sheet but two different columns. I need to ensure the name is there and then make sure its only Q1 2021. Should be 4 but gives me all his Q1 - Q4, so its not doing the second part of the criteria or errors as above.

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Michael Thigpen

    It looks like you're missing a quotation mark around "Todd" above, try this:

    =COUNTIFS({2021 X Projects - Marketing Range 1}, "Todd", {2021 X Projects - Marketing Range 2}, "Q1 2021")

  • jcurry
    jcurry ✭✭

    Appreciative of the Community as a resource! I was able to find the formula I needed by searching the questions

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!