Need help with COUNTIFS formula

jgneely72151

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!

jgneely72151

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

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.

Leslie Merlino

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

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

jgneely72151

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

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

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!

Leslie Merlino

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

jgneely72151

Makes total sense. Thanks for that tip!