i want to know how many times it repeats itself

[cryptohustla]

hi guys im back again and i am learning a lot thanks to you guys anyway i have another problem still related to this thread so i wont create a new thread and instead ask it here .. i want to count how many numbers repeated itself

example

SjeKcnH.jpg

i'm not sure if this is complicated but i really cant figure out how .. i was trying to tweak the codes from the previous thread i had

http://www.excelforum.com/excel-form...of-column.html

with no luck so pls forgive me if i havent learned that much, im still doing some more research but im also having trouble figuring out the correct terms and keywords lol (im not even sure with my thread title)

thank you so much

[azumi]

This troubled me I can't attach my file so i have external links

hope this works

https://simpan.ugm.ac.id/s/PXFIGwXJfpW2CDS

[cryptohustla]

well thanks a lot for your effort really i appreciate your time but i think the fault is on my side as i was not able to explain it well it does do what i want but what i actually want is to just count the consecutive repeated numbers in your example when i type from 70 - 79 it output 4 whereas it should be just 1 because the other 2 70's didnt consecutively repeat itself

U4a1s2A.jpg


i really apologize if i wasnt able to explain it well as english is not my native language.

pls bare with me

thanks

[FlameRetired]

Is this what you mean?

Array-enter this formula in D3 and fill down.

Please Login or Register  to view this content.

If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Row\Col	A	B	C	D
1	31	Criteria
2	42	From	To
3	27	70	79	2
4	25	20	29	3
5	20	40	49	2
6	11	30	39	2
7	5
8	99
9	85
10	70
11	75
12	63
13	85
14	77
15	52
16	33
17	32
18	40
19	45
20	35
21	37
22	100
23	95
24	78

[cryptohustla]

what if i want to select the entire A column? cause i have more than 3000 cells and continuously add some more

[cryptohustla]

also i just wanna add is there any other simplified formula for this? i cant make it work it just gives me #value and i am not sure what i did as i dont undersand the array enter now it gives me this value #n/a

thanks

[FlameRetired]

If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Array formulas are not entered in the regular way. After pasting the formula into the formula bar commit the formula by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

what if i want to select the entire A column? cause i have more than 3000 cells and continuously add some more

You want to avoid using whole columns. Excel would have to perform operations over more than one million cells in each copy of the formula.

A better way would be to extend the range to something slightly larger than you think you would want. Something like

Please Login or Register  to view this content.

And so far I am not able to come up with a way that is significantly simpler.

I will continue to think on it, though.

[FlameRetired]

In the attached find the original proposal on sheet1.

In sheet two I laid the criteria ranges out in columns.

Row\Col	C	D	E	F
2	70	20	40	30
3	79	29	49	39

The numbers in question are still in column A.

Under each of those criteria are helper columns. The formula for those entered in C4 filled down and across is

Please Login or Register  to view this content.

It generates 1s and 0s.

Then in C1 and filled across is a simplified version of the FREQUENCY formula from earlier. Still array-entered.

Please Login or Register  to view this content.

[cryptohustla]

ok after few days of experimenting with it i was able to achieve what i want :D thank you so much

[FlameRetired]

You are welcome. Thank you for the feedback.