I am struggling with a formula in Google Spreadsheet.

Here is what I want to achieve:
If a column in a book is not null and if another column in another book is having a specific value, then counting 1. Then summing up all rows.

Example case: Defect tracking.
If the test cases column 'defectID' has a value and that in another book, this defect does not have the status "Closed" (a specific column), then count it as 1.

Here is the formula I have but it's failing:

=COUNTIFS(ISEMPTY('Test Cases'!N3:N), FALSE, Defects!I3:I,"<>Closed")

Would you know how I can combine functions to have this?


The arguments of COUNTIFS must alternate between ranges and conditions applied to those ranges. The condition "not blank" is expressed as "<>". (And "is blank" would be "=". In the context of this command, the lack of a thing to compare to is understood as comparing to empty string.)

=COUNTIFS('Test Cases'!N3:N, "<>", Defects!I3:I,"<>Closed")


ISEMPTY() is not a supported function in Google Sheets. By the other hand, similar functions like ISBLANK() require a value, not a range/array as an argument.


| improve this answer | |
  • Thanks Norman, this seems to work, however I have an error message because my ranges are not same length so I need to rework the case. – Eric Sou Jul 23 '15 at 6:55
  • Yes, they certainly need to be of the same size for the logic to work. Providing an explicit number of the bottom row is one way; there are others. – user79865 Jul 23 '15 at 6:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.