Is it possible to use the indirect function like this.

try this

Formula:

Please Login or Register  to view this content.

Originally Posted by ChemistB

So, you want
Countif(A1:A5 <= A1), COUNTIF(A1:A5<=A2), COUNTIF(A1:A5<=A3) …
Then COUNTIF(A6:A10<=A6), ….. Correct?

hi Chemist B. Yes exactly!

Originally Posted by Roel Jongman

try this

Formula:

Please Login or Register  to view this content.

Hi Roel. I just get an error, Im not sure if theres something i missed.

Originally Posted by 6StringJazzer

Welcome to the Forum FanAnd22!

First you said that it "produces different results for every single row", which sounds like a description of a problem. Then you say, "When i use a6 i need it to change the range to a6 - a 11 and so forth." which sounds like you want it to produce different results for every single row.

Rather than having us go on a chase for COUNTIF, please describe from the beginning what your data looks like and what result you are trying to get.

If you use

Formula:

Please Login or Register  to view this content.

in row 1, then it will count the occurrences <= A3 in rows 1-5.

If you copy that formula to row 6, it will be

Formula:

Please Login or Register  to view this content.

How is that different than what you want to happen?

Hi 6string. thanks for the reply and thank you, I copied the code

What i get is a6 - a10, yes

The issue is it goes to a7-a10 right after that and changes with every row

I wanted to isolate each range and treat a1-a5 as a group.
I wanted to sort all the values of a1-a5 into consecutive order and so I needed a1-a5 to remain focused on a1-a5 until it reaches a6.
So im pretty much trying to isolate each group and sort the values from smallest to largest. If it changes with each row then its not doing the right comparisons with all available values. So im struggling with that

So Im pretty much trying to get eg:
a1-a5 in consecutive order
a6-a10 in consecutive order
a11-a15 in consecutive order

and so forth.

I was going to use =INDEX($A$1:$A$5,MATCH(ROWS($C$1:C4),$C$1:$C$5,0)) to help me organise everything afterwards. but yeah i got stuck.

Originally Posted by ChemistB

A cleaner formula

Formula:

Please Login or Register  to view this content.

Thank you ChemistB! its works perfectly! thanks

I just need to try get the =INDEX($A$1:$A$5,MATCH(ROWS($C$1:C4),$C$1:$C$5,0)) part

This is more complex than i thought. Can anyone help?

been reading for a few hours

I saw some examples of offset and match being used together rather than index but im imagining i would need to employ offset within match aswell considering the variable will always be changing. Havent found an example thats like mine.


At first I tried to direcetly edit =INDEX($A$1:$A$5,MATCH(ROWS($C$1:C4),$C$1:$C$5,0)) but it turned into a mess

Originally Posted by ChemistB

Is this the same problem or is this a different issue? My formula doesn't require a INDEX/MATCH portion. Are you just looking for different alternatives?

Hi Chemist. No, the solution was perfect. It was a differnt issue. I wanted to output everything in consecutive order with =INDEX($A$1:$A$5,MATCH(ROWS($C$1:C4),$C$1:$C$5,0))

but i couldnt figure out how to apply what you had suggested to this extra step. Sorry for confusion

Originally Posted by jason.b75

A non-volatile somewhere between your orignial and cleaner versions.

Formula:

Please Login or Register  to view this content.

edit:- Just realised what the OP is trying to do after reading post #9 again

Formula:

Please Login or Register  to view this content.

Hi Jason. Thank you. Thank you. its works!
Sorry for the lack of clarification. Really appreciate it


I'll edit post 1 aswell for anyone reading who may have the same problem