COUNTIFS Blank, Not Blank

Options
✭✭✭✭

I need to count the number of contracts in a column that have a BLANK contract status. I'm counting the Contract Amount column but don't need to sum it. Here's the formula I've started:

=COUNTIFS({Contract Amount}, ISBLANK({Contract Status}, @cell))

Any ideas?

• ✭✭✭✭✭✭
edited 01/16/21 Answer ✓
Options

Hey,

Sorry, I did the other formula via mobile app. Your original formula is using the syntax of a SUMIFs, which has the range you want to sum plus criteria range, criteria. COUNTIFS just has criteria range, criteria

=COUNTIFS({Contract Status}, ISBLANK(@cell))

• ✭✭✭✭
Options

Close. A friend of mine found this one to work well:

=COUNTIFS({Contract Amount}, NOT(ISBLANK(@cell)), {Contract Status}, ISBLANK(@cell))

• ✭✭✭✭✭✭
Options

=COUNTIFS({Contract Amount}, {Contract Status}, ISBLANK(@cell))

Kelly

• ✭✭✭✭
Options

It's giving me #invalid operation for that formula

• ✭✭✭✭✭✭
edited 01/16/21 Answer ✓
Options

Hey,

Sorry, I did the other formula via mobile app. Your original formula is using the syntax of a SUMIFs, which has the range you want to sum plus criteria range, criteria. COUNTIFS just has criteria range, criteria

=COUNTIFS({Contract Status}, ISBLANK(@cell))

• ✭✭✭✭✭✭
Options

Did Kelly's answer work for you. It looks correct. If it did, please accept her answer to close the discussion. If it didn't let us know and we'll come up with another option. Thank you for using the Community.

Mark

I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

• ✭✭✭✭
edited 01/17/21
Options

Editing my response. I don't think that worked. It's showing that there are 62 blanks in the Contract Status column, but it's not cross-referencing the Contract Amount column.

What I need it to do is look at the Contract Status column to see if it's blank. If so, count the Contract Amount column. If the Contract Amount column is also blank, don't count this line item.

Ultimately, I'm trying to count all of the times there is a Contract Amount entered where there is no Contract Status.

• ✭✭✭✭✭✭
Options

Try:

=COUNTIFS({Contract Status}, ISBLANK(@cell), {Contract Amount},@cell>0)

Work for you?

Mark

I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

• ✭✭✭✭