Google-sheets – Formula counting based on two conditions

google sheets

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?

Best Answer

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

Remarks

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.

References

Related Topic