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

Billy-Rock

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.

Paul Newcome

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

Billy-Rock

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

Billy-Rock

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

Paul Newcome

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

 

ISTEXT(@cell)

Billy-Rock

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

Billy-Rock

Thank you so much!

Paul Newcome

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

Paul Newcome

Happy to help!