Formula for cell with contents alone apart from subtotal formula.

[Vignesh Veerasamy]

Hi Gurus,

Greetings.

I am working in an excel file where I am using vlookup and find the common cells.
Also I use "SUBTOTAL(3,Axx:Axx) to find the count of the cell contents, but i need to each time filter and remove blanks and check for content of cells as subtotal formula is considering the vlookup formula in the cell and shows the count of entire range how do I get the count of cells with contents alone along with existing formula applicable.

Example :

Common values using vlookup is 3359 line items
Overall list is 30000 but the subtotal formula shows 30000 as vlookupformula is in it, I need to apply filter to check 3359 line items.


Vignesh V

 

[jayalaxmi]

Hi Vignesh,

Can you please share the sample file with the desired output as it would be easier for others to help.

Thanks
Jaya

 

[Vignesh Veerasamy]

Hi Vignesh,

Can you please share the sample file with the desired output as it would be easier for others to help.

Thanks
Jaya

Hi Jaya,

Thank you for response.

I have attached sample file for the same.

Vignesh V

 

[jayalaxmi]

Hi Jaya,

Thank you for response.View attachment 58507

I have attached sample file for the same.

Vignesh V

Hi Vignesh,

PFA the file. See this is what you are looking for.

Please let me know for any help.

Thanks
Jaya

 

[Vignesh Veerasamy]

Hi Vignesh,

PFA the file. See this is what you are looking for.

Please let me know for any help.

Thanks
Jaya

Hi Jaya,

Thanks for reply.

Please find my below detailing of the requirement.



Thank you.

 

[jayalaxmi]

Hey Vignesh,

Use this formula =COUNTIF(D:D,">0") to get 3.

Thanks
Jaya

 

[Vignesh Veerasamy]

H

Hey Vignesh,

Use this formula =COUNTIF(D:D,">0") to get 3.

Thanks
Jaya

Hi Jaya,

Actually this formula works good if there are blank cells and contents are available in random cells,mine contains formula in all cells where I use IFERROR to make it blank but certain cells contain content which need to be shown to know the exact matching data alone.

Thank you.

 

[Vignesh Veerasamy]

Hi Jaya,

Thank you for your swift response.
Output got with aggregate formula.
Above thread can be closed.

 

[bosco_yip]

Or, try this Subtotal formula…............................

In C1, enter formula :

=SUMPRODUCT(SUBTOTAL(3,OFFSET(C3:C31391,ROW(C3:C31391)-ROW(C3),,1)),--(C3:C31391<>" "))

Regards
Bosco

 

[Vignesh Veerasamy]

Or try this Subtotal formula…............................

In C1, enter formula :

=SUMPRODUCT(SUBTOTAL(3,OFFSET(C3:C31391,ROW(C3:C31391)-ROW(C3),,1)),--(C3:C31391<>" "))

Regards
Bosco

Hi Bosco,

Great.Formula is working great.
Would be of great help if any short formula can be used.?
Thank you.

 

[bosco_yip]

Hi Bosco,

Great.Formula is working great.
Would be of great help if any short formula can be used.?
Thank you.

You can use a Named Range instead of showing long formula as in :

1] Select C1 >> Define Name >>

>> Name : FilterCount

>> Refer to : =SUMPRODUCT(SUBTOTAL(3,OFFSET($C$3:$C$31391,ROW($C$3:$C$31391)-ROW($C$3),,1)),--($C$3:$C$31391<>" "))

>> OK

2] Then, in C1 enter :

=FilterCount

Regards
Bosco