# COUNTIFS for how many blank cells in a column, but only if another column has a populated cell

edited 12/09/19

For our smartsheet we keep track of vehicles that are or are not repaired. To keep track of how many "open claims" we have I am trying to count how many blank cells we have in the column that signifies if something is complete, but when I try to just count those cells it counts the automatic additional rows on the bottom of the sheet as well. So I need a formula that says to count blank cells, but only if another cell is populated. Here is the formula I have currently:

=COUNTIFS([Rep Completion Confirmation]:[Rep Completion Confirmation], "", VIN:VIN, 0)

When I do this I just get a count of 0. Here are the screenshots to help explain my situation better.

• ✭✭✭✭✭✭

That is because non of the VIN #'s are zero. Replace

0

with

ISTEXT(@cell)

.

=COUNTIFS([Rep Completion Confirmation]:[Rep Completion Confirmation], "", VIN:VIN, ISTEXT(@cell))

• Thanks for the reply! I am entering in that formula and getting #incorrect argument. I am trying to get the result in one single cell.

=COUNTIFS([Rep Completion Confirmation]:[Rep Completion Confirmation], "", ISTEXT(VIN:VIN))

• Also, I was testing it to see if it worked if I selected a single cell and I still get zero??? Another screenshot

• ✭✭✭✭✭✭

That is because you need to use the @cell reference. Try entering it exactly as I did.

ISTEXT(@cell)

• Ahhhhh... I thought you were just using that as a place holder

• Thank you so much!

• ✭✭✭✭✭✭

That is used to tell the formula to run on each cell individually instead of the range as a whole.

• ✭✭✭✭✭✭

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!