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?

Oct 24, 2022 in Others 341 views

## 1 answer to this question.

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

```=AGGREGATE(9,7,B2:B6)
```  • 63,160 points

## Counting distinct values in excel - frequency function

You can use COUNTIF to count the ...READ MORE

## Alternatives to Subtotal function in excel

In an unused column to the right ...READ MORE

## How to trick an Excel function that wants a column as input to accept a list of values as if these were in a column

Use VSTACK: vstack to make an array: Use it as value ...READ MORE

## Count Function for identifying the Unique values in Excel

If the 9th row is empty: =COUNTA(UNIQUE(TOCOL(F8:R10)))-1 If not ...READ MORE

## Calculate Birthdate from an age using y,m,d in Excel

Hi To Calculate the date, we can ...READ MORE

## Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...READ MORE

## IF - ELSE IF - ELSE Structure in Excel

In this case, you can use nested ...READ MORE