Is it possible to divide countifs formulas

Options

I'm trying to divide countifs formulas that are using two criterias! I've gotten it to produce a number, but the resulting number is incorrect!!

I want to find the % of 'Yes' values in the Opened For field, for the Contact & Support Team. So

#of Customer & Support Opened for' Yes / Total #of Customer & Support 'Opened for' for


This is the formula that is delivering the incorrect %

=COUNTIFS([Opened For (populated with accurate EE name)]:[Opened For (populated with accurate EE name)], "Yes", Team:Team, ="Contact & Support") / COUNT([Opened For (populated with accurate EE name)]:[Opened For (populated with accurate EE name)], Team:Team, ="Contact & Support")

Best Answer

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    edited 09/08/23 Answer ✓
    Options

    @AGorgei

    This should do the trick if you're after the %age of "Yes"' from the Contact & Support team:

    =COUNTIFS([Opened For (populated with accurate EE name)]:[Opened For (populated with accurate EE name)], "Yes", Team:Team, "Contact & Support") / COUNTIFS(Team:Team, "Contact & Support")

    If you're wanting the %age for Yes AND Contact & Support as a total of all lines:

    =COUNTIFS([Opened For (populated with accurate EE name)]:[Opened For (populated with accurate EE name)], "Yes", Team:Team, "Contact & Support") / COUNTIFS([Opened For (populated with accurate EE name)]:[Opened For (populated with accurate EE name)], <>"")

    Let me know if there are any problems/questions!

Answers

  • Jason Albrecht
    Jason Albrecht ✭✭✭✭✭✭
    Options

    Hi @AGorgei

    maybe try this?

    =COUNTIFS([Opened For (populated with accurate EE name)]:[Opened For (populated with accurate EE name)], "Yes", Team:Team, ="Contact & Support") / COUNTIFS([Opened For (populated with accurate EE name)]:[Opened For (populated with accurate EE name)], Team:Team, ="Contact & Support")

    It looks like the 2nd CountIFS function was missing the IFS?

    Hope this helps and that you have a great day,

    Jason Albrecht MBA, MBus(AppFin), DipFinMgt

    LinkedIn profile - Open to work

  • AGorgei
    AGorgei ✭✭
    edited 09/08/23
    Options

    Hi @Jason Albrecht thanks, I tried that one before too...getting an #Invalid Operation error. I've also tried the second Count as as COUNTIF and that throws a #Incorrect Argument Set error :-(

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    edited 09/08/23 Answer ✓
    Options

    @AGorgei

    This should do the trick if you're after the %age of "Yes"' from the Contact & Support team:

    =COUNTIFS([Opened For (populated with accurate EE name)]:[Opened For (populated with accurate EE name)], "Yes", Team:Team, "Contact & Support") / COUNTIFS(Team:Team, "Contact & Support")

    If you're wanting the %age for Yes AND Contact & Support as a total of all lines:

    =COUNTIFS([Opened For (populated with accurate EE name)]:[Opened For (populated with accurate EE name)], "Yes", Team:Team, "Contact & Support") / COUNTIFS([Opened For (populated with accurate EE name)]:[Opened For (populated with accurate EE name)], <>"")

    Let me know if there are any problems/questions!

  • AGorgei
    AGorgei ✭✭
    Options

    @Nick Korna The first one did it! You ROCK! THANK YOU!!!!!

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Options

    No problem, happy to have helped! 😊

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!