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 in Others by Kithuzzz
• 20,660 points
28 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 by narikkadan
• 37,660 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 in Others by narikkadan
• 37,660 points
31 views
0 votes
1 answer

Alternatives to Subtotal function in excel

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

answered Nov 10 in Others by narikkadan
• 37,660 points
33 views
0 votes
1 answer

What is a name function in JavaScript & how to define it?

A named function declares a name as ...READ MORE

answered Mar 7, 2019 in Others by Frankie
• 9,830 points
1,697 views
0 votes
1 answer

How to create a function in Dart language?

Hi@akhtar, There are many function types available in ...READ MORE

answered Jul 22, 2020 in Others by MD
• 95,380 points
263 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 in Others by Edureka
• 13,640 points
140 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 in Others by Edureka
• 13,640 points
238 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 in Others by Edureka
• 13,640 points
106 views
0 votes
1 answer

IF - ELSE IF - ELSE Structure in Excel

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

answered Feb 18 in Others by gaurav
• 22,040 points
70 views
0 votes
1 answer
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 in Others by narikkadan
• 37,660 points
44 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