=Aggregate( nightmare!

[ExcelMBA]

Really hope you can help me on this.</SPAN>
</SPAN>
I`m using Excel 2013, I need to plug into another formula an array that differentiates hidden and visible rows (filtered and not filtered rows)
</SPAN></SPAN>
The way I was approaching this was creating a dummy column with the aggregate function like so:</SPAN>
</SPAN>
=aggregate(3,3,</SPAN> [@[any_other_column]]</SPAN>) </SPAN></SPAN>
The idea was this should`ve outputted 1s every time there was a visible row present and hidden rows output 0s. Then I would`ve used this dummy column of 1s and 0s as a reference for my main formula.</SPAN></SPAN>

But it`s not working! Some of the outputs in hidden rows (filtered rows) are 1s!! isn`t aggregate supposed to omit hidden rows? </SPAN></SPAN>

I`m sure it`s not working because when I sum up the entire dummy column I get a result of n+1 from the visible rows: e.g. I filter the table and obtain 5 visible rows, the sum of dummy column is 6. </SPAN></SPAN>

Ugggghh frustrating!? Mainly because sometimes it works and sometimes it doesn`t!</SPAN></SPAN>

Anybody know what`s going on? Or maybe you can give me another idea on how use other formulas that omit hidden or filtered cells. Thanks!!!
</SPAN></SPAN>
Anybody know what`s going on? </SPAN></SPAN></SPAN></SPAN></SPAN>

 

[barry houdini]

Welcome to MrExcel!

That works for me - I'm not sure why it doesn't work for you

You can also use SUBTOTAL and you can get an array of 1/0 values in a single function if you use this formula

=SUBTOTAL(3,OFFSET(Range,ROW(Range)-MIN(ROW(Range)),0,1,1))

 

[ExcelMBA]

Thank you Barry! Works great.