EXCEL data counting

Question

I currently have this formula that counts data from cells in Column B and C. I need to amend the formula so it won't count if a cell in column D is displaying the word "N/A"

As you can see in the example sheet, "L03" in the table is being counted as "4" however this needs to be zero as N/A is being displayed in column D against code "L03"

=COUNTIFS(B:B,"1",C:C,"L01")

The above code is what I have to count everything - I have tried everything and not getting anywhere. Please Help.

Answer 1

Add the exclusion using "<>"

=COUNTIFS(B:B,"1",C:C,"L01",D:D, "<>N/A")

Note this works for your data, as the N/A is typed in the cells. If the N/A is the actual error #N/A then use this:

=COUNTIFS(B:B,H$6,C:C,$G7,D:D, "?*")