I am trying to achieve this in Google Sheets, but nothing is working out.

Here is the table

In the column B, I want to count all non-0 cells. This is easy and I have done that.

In column C, I want to count all non-0 cells in column C, only if the cell to the left is empty. I have been able to do this too.

But now in column D, I want to count non-0 cells in column D, if and only if both the adjacent cells in column B and C are empty. I am not able to do this.

In cell D9 I should be getting a 1 - because only cell D8 has a value where B8 and C8 are empty.

How do I achieve this?

Excuse the Excel screenshot, but I want to achieve this in GoogleSheets.

Please note, I realise I can do this with if statements. However in the real dataset, I have 35 columns, so you can see where it gets cumbersome to use so many if statements.

Thank you in advance!

CodePudding user response：

in D9 try:

=COUNTA(IFNA(FILTER(D4:D8, D4:D8<>"", C4:C8="", B4:B8="")))