Ignore text values in subtotal function

0 votes

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 by Kithuzzz
• 38,010 points
552 views

1 answer to this question.

0 votes

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

=AGGREGATE(9,7,B2:B6)

enter image description here

enter image description here

answered Oct 24, 2022 by narikkadan
• 63,700 points

Related Questions In Others

0 votes
1 answer

Counting distinct values in excel - frequency function

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

answered Oct 20, 2022 in Others by narikkadan
• 63,700 points
310 views
0 votes
1 answer

Alternatives to Subtotal function in excel

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

answered Nov 10, 2022 in Others by narikkadan
• 63,700 points
528 views
0 votes
1 answer

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

answered Mar 18, 2023 in Others by narikkadan
• 63,700 points
306 views
0 votes
1 answer

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

answered Apr 6, 2023 in Others by narikkadan
• 63,700 points
327 views
0 votes
1 answer

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

Hello, yes u can find your birthdate using ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
1,689 views
0 votes
1 answer

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

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

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
1,551 views
0 votes
0 answers

Convert Rows to Columns with values in Excel using custom format

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

Feb 17, 2022 in Others by Edureka
• 13,670 points
756 views
0 votes
1 answer

IF - ELSE IF - ELSE Structure in Excel

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

answered Feb 18, 2022 in Others by gaurav
• 23,260 points
2,620 views
0 votes
1 answer

Excel formula for searching two text in one cell and return values based on the result

You can include a second IF within ...READ MORE

answered Oct 10, 2022 in Others by narikkadan
• 63,700 points
1,009 views
0 votes
1 answer

Excel trim function is removing spaces in middle of text - this was unexpected (?)

Create a UDF that uses VBA's version ...READ MORE

answered Oct 18, 2022 in Others by narikkadan
• 63,700 points
625 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP