Exclude Hidden Rows from being used within a formula

[Hughy649]

Good afternoon

My array is all data, some of it, at times, I want to hide and not be included in the calculations. I searched the internet and I believe that Subtotal should work to hide any hidden rows from being included in a formula. However, I can't figure out where to include in it the formula - front, within, the array itself?!?

I use excel 2010 and the formulas are below. Any help would be greatly appreciated.

Cheers

Andy


=SMALL(BJ108:BJ117,COUNTIF(BJ108:BJ117,0)+1) - finding the smallest # not including zero's

=(SUM(BJ108:BJ117)-AX111-AY111)/(AV109-2) - finding the weighted average

 

[Aladin Akyurek]

One last issue, being counting the number of unhidden cells in range S20:S54, with values over 0.

This looked like it should work but to no avail.

=SUMPRODUCT(SUBTOTAL(103,OFFSET(S20:S54,ROW(S20:S54)-ROW(S20),0,1)),--(S20:S54>0))

Thanks in advance.

Andy

Try...

=SUMPRODUCT(SUBTOTAL(102,OFFSET(S20,ROW(S20:S54)-ROW(S20),0,1)),--ISNUMBER(S20:S54),--(S20:S54>0))

 

[Hughy649]

Thanks Aladin

It worked well on most columns, but not all, then I realized that some numbers were coming over as "x" and not just x. Once corrected, all was great.

Many thanks.

Andy

 

[Aladin Akyurek]

Thanks Aladin

It worked well on most columns, but not all, then I realized that some numbers were coming over as "x" and not just x. Once corrected, all was great.

Many thanks.

Andy

Great to hear that all is sorted out. Thanks for providing feedback.