COUNTIFS Blank, Not Blank

Nick Allgauer

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?

Kelly Moore

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

Nick Allgauer

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

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

Kelly Moore

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

Kelly

Nick Allgauer

It's giving me #invalid operation for that formula

Kelly Moore

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

Mark Cronk

Hi @Nick Allgauer ,

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

Nick Allgauer

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.

Mark Cronk

Hi @Nick Allgauer ,

Try:

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

Work for you?

Mark

Nick Allgauer

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

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

Heather L

Is there an equivalent formula that can be used for the Sheet Summary? I'd like to, at the sheet level, count Column B, Item X (there are also Item W, Item Y and Item Z so this formula would be repeated for each Item in Column B), by Column C, Date (if the date is entered, it is counted and if it has not been entered, it does not get counted).