Skip to content
  • Try Smartsheet

    • Formulas and Functions

    Formulas and Functions

    Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

    Need help with COUNTIFS formula

    ✭✭✭✭✭✭
    edited 08/27/20 in Formulas and Functions

    Hello!

    I have 2 fields, "Confirmed" (checkbox) and "Status" (drop-down values) to evaluate.

    In a 3rd field "# Waiting to be processed", I need a formula to return only the count of those records where "Confirmed" is checked and the "Status" field is blank which means "Status" has not been assigned yet

    Essentially, this formula should give me the number of records waiting to be process that's been confirmed by requestor.

    I know I should be using COUNTIFS formula but having trouble stringing everything together.

    Would you please advise how to write this formula?

    Thanks!

    Tags:
    · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Best Answers

    • ✭✭✭✭✭✭
      edited 08/27/20 Answer ✓

      So sorry...i should have mentioned that i was doing a roll up and using cell references in another sheet. So i wrote the formula as such but got unparsanble


      =COUNTIFS({Fall 2020 AGNP-AC Placements Range 3}, isblank @cell), {Fall 2020 AGNP-AC Placements Range 6},@cell=1

      · Share on FacebookShare on Twitter
    • ✭✭
      Answer ✓

      You don't have all of the parenthesis you need. Try to copy/paste this:

      =COUNTIFS({Fall 2020 AGNP-AC Placements Range 3}, ISBLANK(@cell), {Fall 2020 AGNP-AC Placements Range 6}, @cell = 1)

      You did not have a parenthesis in your ISBLANK formula and you did not have the one at the end of the formula.

      · Share on FacebookShare on Twitter

    Answers

    • ✭✭

      You'd need a nested COUNTIFS and ISBLANK formula. Try this one:

      =COUNTIFS(Status:Status, ISBLANK(@cell), Confirmed:Confirmed, @cell = 1)

      · Share on FacebookShare on Twitter
    • ✭✭✭✭✭✭
      edited 08/27/20 Answer ✓

      So sorry...i should have mentioned that i was doing a roll up and using cell references in another sheet. So i wrote the formula as such but got unparsanble


      =COUNTIFS({Fall 2020 AGNP-AC Placements Range 3}, isblank @cell), {Fall 2020 AGNP-AC Placements Range 6},@cell=1

      · Share on FacebookShare on Twitter
    • ✭✭
      Answer ✓

      You don't have all of the parenthesis you need. Try to copy/paste this:

      =COUNTIFS({Fall 2020 AGNP-AC Placements Range 3}, ISBLANK(@cell), {Fall 2020 AGNP-AC Placements Range 6}, @cell = 1)

      You did not have a parenthesis in your ISBLANK formula and you did not have the one at the end of the formula.

      · Share on FacebookShare on Twitter
    • ✭✭✭✭✭✭

      Yes, this worked like a charm! Thank you so much! Figuring out the logic behind which function to use and then constructing the formula is mind boggling to me. I'm just used to really simple formulas as it relates to sums and averages. I'll have to look more into learning how to use more complex formulas. I really appreciate your help!

      · Share on FacebookShare on Twitter
    • ✭✭

      You are welcome!! It can be confusing. It helps to whiteboard the formula and write out the logic in words sometimes. :-)

      · Share on FacebookShare on Twitter
    • ✭✭✭✭✭✭

      Makes total sense. Thanks for that tip!

      · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Help Article Resources

    Create and edit formulas in Smartsheet

    Formula combinations for cross sheet references

    Smartsheet functions list

    Quick Links

    Categories

    Want to practice working with formulas directly in Smartsheet?

    Check out the Formula Handbook template!

    Trending in Formulas and Functions