how can use subtotal formula in MS-Excel for text category range

1
votes

I have a Table in Worksheet, that has a column with Text number category and contains integer values that start with 0. Same as "02".

I want gaining the value which used for filtering Table in that column.

According to I need one of each visible Cell Values in the column, used below formula:

=SUBTOTAL(5,tblInsurance[DSK_LISTNO])

But returned 0 as result! Ant not look at cell values.

And I can't using =SUBTOTAL(5,value(tblInsurance[DSK_LISTNO])) for passing range values, to function as Integer.

My Table hasn't Total row.

So, How can I gaining the filtered value so take filtered Table by a column?

2
excelvba
can you convert them from text to integer by doing F2 on the cells or by any other method – Apurv Pawar
Values should start with 0. So cant convert source values. – mgae2m

2 Answers

1
votes

This is not easy to do. The best way of solving the problem is to store the integers as integers and not as text. You can use cell formatting to place the 0 in front of the integer.

Then the table will LOOK the same but be stored as integer and the SUBTOTAL function will work.

0
votes

I found bellow formula:

=INDEX(C2:C19,MIN(IF(SUBTOTAL(3,OFFSET(C2,ROW(C2:C19)-ROW(C2),0)),ROW(C2:C19)-ROW(C2)+1)))

enter image description here

Works greatly useful with nice solution. so as such similar solutions in several problems I experienced, seems bellow reference is much valuable.

Ref: https://www.extendoffice.com/documents/excel/4217-excel-find-value-of-first-visible-cell.html