Forum Discussion
count consecutive occurrences if multiple criteria
I am counting times a value appears consecutive but i need it to only count if multiple criteria met. for example below i counting times "mike" appears consecutively. but i want "mike" along with match id or rack too
I am using =FREQUENCY(IF(C2:C9="mike",ROW(C2:C9)),IF(C2:C9<>"mike",ROW(C2:C9))) but i need to add more criteria. i was playing with it but i cant figure it out. link below. help!
https://1drv.ms/x/s!AnFi6uGE1rekmUJlqWfJEK1lpedT?e=hR2b8v
- solved what i wanted. posting response in case someone wants
=FREQUENCY(IF(C2:C9="mike",IF(D2:D9<>1.5,ROW(C2:C9))),IF(C2:C9<>"mike",IF(D2:D9=1.5,ROW(C2:C9))))
3 Replies
- solved what i wanted. posting response in case someone wants
=FREQUENCY(IF(C2:C9="mike",IF(D2:D9<>1.5,ROW(C2:C9))),IF(C2:C9<>"mike",IF(D2:D9=1.5,ROW(C2:C9)))) How about this online tool?
http://e.anyoupin.cn/EData/?s=basic_count_consecutive_occurences
create temp table aa as
select *,lag(player) over (),iif(lag(player) over ()!=player or rowid=1,rowid,'') grp from basic_count_consecutive_occurences;
create temp table bb as
select *,fillna(grp) grp2 from aa;
select * from bb;
cli_add_html~mike,rack=1;
select grp2,player,rack,count(grp2)-1 times from bb where player like 'mike' and rack=1 group by grp2;
- thank you for the reply. im looking for a formula rather than using script
