Ignore text values in subtotal function

Question

Excel-Sheet:

      A            B            C            D             E
1                1.200
2  Product A      500 
3  Product B      400
4  Product C      OK
5  Product D      #NA
6  Product E      300
7
8

---

In the above table, I have a list of products in Column A and some data about the products in Column B.

In Cell B1 I want to calculate the subtotal of Column B using =SUBTOTAL(9,B2:B6).

However, now I have the issue that Column B not only consists of numbers.
It can also have the data type text (OK, NA). Therefore, the result in Cell B1 currently is #NA.

Is there any kind of formula that I could use so only the number of data is considered and the result is 1.200 as in the table above?

Answer 1

Use 7 as the second criterion in AGGREGATE instead of 6 as it will also exclude hidden rows:

=AGGREGATE(9,7,B2:B6)