CODEWITHSUNDEEP
excel
madyykk
madyykk

Reputation: 13

Count number of next cells based on criteria

Can we have a formula or a VBA code for counting number of next cells based on criteria.

For instance if we have a list

A
A
B
A
C

and we would like to count number of A's after A (the criteria), so in this case, it would be 1

Upvotes: 0

Views: 71

Answers (3)

Karpak
Karpak

Reputation: 1927

The simplest possible solution that I can think for this problem is

=IF(A2<>A1,0,INDEX(MATCH(FALSE,A2:A100=A1,0),1,1)-1)

enter image description here

Upvotes: 0

CallumDA
CallumDA

Reputation: 12113

If you just want to know the number of times A is followed by A then you can use a formula like this

=COUNTIFS(A1:A5,"A",A2:A6,"A")

Assuming your data is in A1:A5

This will also allow you to find out how many times A is followed by B or any other combination by changing the criteria

Upvotes: 2

Scott Craner
Scott Craner

Reputation: 152505

Use SUMPRODUCT():

=SUMPRODUCT(($A$1:$A$5="A")*($A$2:$A$6=$A$1:$A$5))

enter image description here

Upvotes: 1

Related Questions