LinkBack URL
About LinkBacks
Hi can someone please tell me why =AGGREGATE(2,5,A$1,A16)+1 fails to number the rows 1-16. Instead it gives me seemingly random 1s & 2s. Thanks!
Hi can someone please tell me why =AGGREGATE(2,5,A$1,A16)+1 fails to number the rows 1-16. Instead it gives me seemingly random 1s & 2s. Thanks!
We need more information.
What are you trying to achieve?
What cell(s) contain your formula?
What is contained in Col_A?
Can you post some sample data and the results you want to see...based on that data?
I don't know what you are trying to do. If you have values in A1:A16, if you enter this in say column C and copy down, it will give you a progressive count as you go down the column. All that this is doing is counting the values as the formula is dragged down the column. (A$1:A1 being the expanding range being counted)
Formula:
Please Login or Register to view this content.
The first argument is the function to be used. 2 is COUNT
The second argument is an option and 5 is to ignore hidden rows.
The third argument in the function requires a RANGE. You have given only 1 cell as a range and it will not expand.
The fourth argument in the function is the position in the array....with 2 chosen as the action to perform (count) the 4th argument doesn't appear to affect the result.
The +1 that you have just adds 1 to the count.
Last edited by newdoverman; 09-27-2014 at 03:51 PM.
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
I've a table with hidden rows. B4-B2341 is populated with urls. I'm trying to use the aggregate function to populate column A with row numbers, starting with 1 at A4 and ignoring hidden rows. So far no success with =AGGREGATE(2,5,A$1,A1)+1 in A4 & copied to A2341. Thanks for your help!!!!!!!!!!!!
Maybe this small sample will help. There are hidden rows and a count given in column C. If you unhide the hidden rows, you will see the count increase.
Well, this is a mystery to me. I duplicated yours but get different results. I've attaché it so you can see what I mean. Why??? Many, many thanks for your help!!
i cant use aggregate as i dont have >2007 but do you want to number the filtered rows so ifthe filter shows only 20 things they are numbered 1 throu 20?
if so try this
a4=
=IF(SUBTOTAL(3,$B4),1+MAX($A$3:A3),"")
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
You need to use function 3 instead of 2
Formula:Please Login or Register to view this content.
Good grief! I could've sworn I'd tried that but obviously not. You made my weekend. Many, many thanks!!!
Thank you for the feedback.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks