CODEWITHSUNDEEP
excelvbacounting
Nachtstiel
Nachtstiel

Reputation: 3

VBA Excel count cells in a range with a specific value

I am looking for a way to count the number of cells in a range with a specific value, but will skip cells that are next to the previous cell if they have the same value. So that if I have a series like 1,2,3,3,4,3 the result for the number of 3's would be 2.

Upvotes: 0

Views: 2832

Answers (1)

Scott Craner
Scott Craner

Reputation: 152505

Use this:

=COUNTIF(A:A,3)-COUNTIFS(A1:A1040000,3,A2:A1040001,3)

enter image description here

Edit:

The following formula works just as well:

=COUNTIFS(A1:A1040000,3,A2:A1040001,"<>" & 3)

In vba:

Dim output as Long
With AcitveSheet
    output = Application.WorksheetFunction.Countifs(.Range("A1:A1040000"),3,.Range(A2:A1040001),"<>" & 3)
End With
Debug.Print output

Upvotes: 2

Related Questions