Need help with COUNTIFS formula

jgneely72151
jgneely72151 ✭✭✭✭✭
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:

Best Answers

  • jgneely72151
    jgneely72151 ✭✭✭✭✭
    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

  • Leslie Merlino
    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.

Answers

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

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

  • jgneely72151
    jgneely72151 ✭✭✭✭✭
    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

  • Leslie Merlino
    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.

  • jgneely72151
    jgneely72151 ✭✭✭✭✭

    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!

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

  • jgneely72151
    jgneely72151 ✭✭✭✭✭

    Makes total sense. Thanks for that tip!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!