How to sum with hidden rows inbetween?

[Jyggalag]

Hi all,

I have this setup at the moment:

However, when I search for "14-06-2022" I get a sum of 9964136,14 because my SUM formula takes all the hidden rows into account

When I plus all the numbers I get the correct amount of 372344,76

But it takes a lot of time to write "A1 + A101 + A102 + A103 + A104 etc...". Does anybody have a formula or technique to avoid calculating the hidden rows as well?

Thank you!

 

[Peter_SSs]

Something like this?

22 11 08.xlsm
	A								I
1	Hdr1								Hdr9
2	a								2
6	a								1
8	a								5
10	a								6
11
12
13									14
Sum Visible

Cell Formulas
Range	Formula
I13	I13	=AGGREGATE(9,5,I2:I10)

 

[Jyggalag]

Something like this?

22 11 08.xlsm
	A								I
1	Hdr1								Hdr9
2	a								2
6	a								1
8	a								5
10	a								6
11
12
13									14
Sum Visible

Cell Formulas
Range	Formula
I13	I13	=AGGREGATE(9,5,I2:I10)

I found out that SUBTOTAL(9; X:Y) also works!

But this does too! Thank you so much Peter!

 

[Peter_SSs]

You're welcome.