Sum filtered raw only

[Jagat Pavasia]

dear sir,
I have amount in C5:C28 and D5:D28.
I want total of C5:C28 in C2 and D5:D28 in D2.

but when filtered in other RAW then total only which shows.

For example :

If I filtered then :

So , which I formula or VBA code should apply ? please suggest me

 

[YasserKhalil]

Try this UDF

Function SumFilteredRange(ByVal rng As Range) As Double
    Dim cel As Range, total As Double
    If rng Is Nothing Then SumFilteredRange = 0: Exit Function
    For Each cel In rng
        If cel.EntireRow.Hidden = False Then total = total + cel.Value
    Next cel
    SumFilteredRange = total
End Function

 

[Jagat Pavasia]

Try this UDF

Function SumFilteredRange(ByVal rng As Range) As Double
    Dim cel As Range, total As Double
    If rng Is Nothing Then SumFilteredRange = 0: Exit Function
    For Each cel In rng
        If cel.EntireRow.Hidden = False Then total = total + cel.Value
    Next cel
    SumFilteredRange = total
End Function

not working

 

[Jagat Pavasia]

Try this UDF

Function SumFilteredRange(ByVal rng As Range) As Double
    Dim cel As Range, total As Double
    If rng Is Nothing Then SumFilteredRange = 0: Exit Function
    For Each cel In rng
        If cel.EntireRow.Hidden = False Then total = total + cel.Value
    Next cel
    SumFilteredRange = total
End Function

suggest deeply

 

[YasserKhalil]

You can use it in the worksheet like that

=SumFilteredRange(O2:O225)

 

[Jagat Pavasia]

=SumFilteredRange(O2:O225)

not working

 

[YasserKhalil]

Have you enabled macros? If yes, then attach sample of the workbook to test on.

 

[awoohaw]

Why can't you use the AGGREGATE function with option 4 (ignore nothing):

(my numbers are made up, i didn't want to recreate your scenario exactly).

Mr Excel Questions 71.xlsm
	A	B	C	D	E
1
2
3			179	159
Jagat Pavasia

Cell Formulas
Range	Formula
C3:D3	C3	=AGGREGATE(9,4,C5:C28)

 

[YasserKhalil]

@awoohaw
It should be 5 not 4

 

[awoohaw]

@awoohaw
It should be 5 not 4

Thanks for the clarification. Yes, 5 for ignoring all hidden rows. 4 ignores everything. The OP does not make it clear what is wanted in original post.