How to count occurrences of two or three numbers in different rows?

[kirvis12]

How to count occurrences of two or three numbers in different rows?

After value 4, goes value 2. I looking for formula to count, how many times that occurs in the table. Best, if formula is flexible, where you can input different values. If say, value 7, then value 2. Maybe it is possible to count sequence of three numbers.
I tried using formula =COUNTIFS(), but unsuccessful.


Thank you for help.

[adhawan06]

Hi Krivis,

Assuming you wanted to get the count of B Column and then put the below formula in column C2 and then drag it to the last.

Please Login or Register  to view this content.

[adhawan06]

Or else if you want the result like below, then use the following function in

Please Login or Register  to view this content.

Countif.PNG



EDIT - Now If you see both formulas, there is slight difference of absolute reference. In the Post 1, I have selected the entire column which will give me total number of repetitive in front of each value (In your case 1 will always show 4819), whereas In post 2, I have only used the range till current rows from the first value. (it will tell you how many time that the number has been repeated in the above range)

[kirvis12]

Hi adhawan06,
I want one cell, where the count will display. Say, if count how many times 4 then 2, answer count 76.

Thank you

[kirvis12]

Hi adhawan06,
Thank you for replay. Then using =COUNTIFS(B2:B9622,4), result is 535. If using formula =COUNTIFS(B2:B9622,$E$3,B2:B9622,$G$3), where $E$3 = 4, and $G$3 = 2, I get 0.
So, to count how many times combination 4 then 2 occurres, what need be done?

Thank you

[adhawan06]

Hi Kirvis,

Added one Helper column in Column C where the formula will be in C2 =

Please Login or Register  to view this content.

Then, Go to D2 and you can now easily count that how many time "Yes" has appeared

Please Login or Register  to view this content.

Please let me know if it solve your purpose.

[kvsrinivasamurthy]

Pl see file.
Try this

Please Login or Register  to view this content.

[kirvis12]

Hi adhawan06,
Yes, thank you, it counts correctly. I wondering if the same could be done without additional colum.
Thank you

[adhawan06]

Hi Kirvis,

As mentioned by kvsrinivasamurthy, you can also get the same result by singly cell formula (no need to have helper column)

Pl see file.
Try this

Please Login or Register  to view this content.

I am just changing and fixing the criteria range as per your requirement (Please dont mind kvsrinivasamurthy)

As you mentioned, $E$3 = 4 and $G$3 = 2, then the formula will be

Please Login or Register  to view this content.

Please confirm if it makes you comfortable.

Cheers!!!

[kirvis12]

Hi adhawan06,
Yes, yes, yes, thank you, it counts correctly. I just change formula a bit
< =SUMPRODUCT((B1:B9620=$D$2)*(B2:B9621=$E$2))>.
Wondering why the selection differ, B1:B9620 and B2:B9621. I tried to put B:B, returns 0.
Thank you

[kirvis12]

Hi adhawan06,
Oooo... Understood. It needs start from next row.
Thank you

[adhawan06]

Hi Kirvis,

As you mentioned that you wanted to get the count if 2 is following 4. So that's why the formula first is looking for 4 then for 2 in second array.

If you have got your answer, please take few seconds marking this thread as SOLVED and say thanks to the users who helped you by adding *Add Reputation.



Cheers!!!

Anil Dhawan

[kirvis12]

Hi adhawan06 and kvsrinivasamurthy
Thank you, excellent. How about three numbers. Say 3,2,3
Thank you

[kirvis12]

Hi adhawan06 and kvsrinivasamurthy
Thank you, looks like, formula < =COUNTIFS(B1:B9620,$D$2,B2:B9621,$E$2)> works good to. That I didn’t do, is criteria range2 must offset criteria range1.
Thank you

[adhawan06]

Looks Like you also posted on some other forum.

Your post does not comply with Rule 5 & 8 of our Forum RULES.

Forum Rules
Please read and adhere to these simple rules!


5. Don't duplicate threads. If you have posted the question in one forum, do not post it again in another forum. You are duplicating efforts. If you feel the thread is in the wrong forum and needs to be moved to another forum, PM a mod / admin to do it for you.

8. Don't cross-post without a link. Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post. Expect cross-posts without a link to be closed.


http://www.mrexcel.com/forum/excel-q...rent-rows.html.

Also, If you have got your answer, kindly mark this thread as SOLVED and say thanks to the person who helped you by adding *Add Reputation.

Cheers!!!

[kirvis12]

Hi adhawan06 and kvsrinivasamurthy
Thank you, looks like, formula
<=COUNTIFS(B1:B10000,$C$2,B2:B10001,$D$2,B3:B10002,$E$2)>
Works great. Thank you both for great help.
Thank you

[samba_ravi]

Can try
=SUM(INDEX(((B1:B9621=4)+(B2:B9622=2)=2)*1,0))

[adhawan06]

Looks Like you also posted on some other forum.

Your post does not comply with Rule 5 & 8 of our Forum RULES.

Forum Rules
Please read and adhere to these simple rules!


5. Don't duplicate threads. If you have posted the question in one forum, do not post it again in another forum. You are duplicating efforts. If you feel the thread is in the wrong forum and needs to be moved to another forum, PM a mod / admin to do it for you.

8. Don't cross-post without a link. Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post. Expect cross-posts without a link to be closed.


http://www.mrexcel.com/forum/excel-q...rent-rows.html.

Also, If you have got your answer, kindly mark this thread as SOLVED and say thanks to the person who helped you by adding *Add Reputation.

Cheers!!!

kindly mark this thread as SOLVED and say thanks to the person who helped you by adding *Add Reputation.

[samba_ravi]

=sum(index(((b1:b9621=4)+(offset(b1:b9621,1,0)=2)=2)*1,0))

[kirvis12]

=sum(index(((b1:b9621=4)+(offset(b1:b9621,1,0)=2)=2)*1,0))

nflsales, great, the use of offset is exellent.