Need help with COUNTIFS formula
Hello!
I have 2 fields, "Confirmed" (checkbox) and "Status" (dropdown 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!
Best Answers

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 AGNPAC Placements Range 3}, isblank @cell), {Fall 2020 AGNPAC Placements Range 6},@cell=1

You don't have all of the parenthesis you need. Try to copy/paste this:
=COUNTIFS({Fall 2020 AGNPAC Placements Range 3}, ISBLANK(@cell), {Fall 2020 AGNPAC 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)

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 AGNPAC Placements Range 3}, isblank @cell), {Fall 2020 AGNPAC Placements Range 6},@cell=1

You don't have all of the parenthesis you need. Try to copy/paste this:
=COUNTIFS({Fall 2020 AGNPAC Placements Range 3}, ISBLANK(@cell), {Fall 2020 AGNPAC 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.

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. :)

Makes total sense. Thanks for that tip!
Help Article Resources
Categories
Check out the Formula Handbook template!