Excel function: countif

Count specific values

The following example uses the sub testData to fill a few values (foo, bar and baz) into the cells in column 1 (rows 3 through 8).

It then uses =countif(…) to count the occurences of foo, bar and baz in the test data.

option explicit

sub main() ' {

    testData

  '
  ' Count values of foo, bar and baz:
  '
    cells(1, 2) = "foo"
    cells(1, 3) = "bar"
    cells(1, 4) = "baz"

    range(cells(2,2), cells(2,4)).formulaR1C1 = "=countif(r3c1:r8c1, r[-1]c)"

end sub ' }

sub testData() ' {
 '
 '  Clear active sheet's data:
 '
    activeSheet.usedRange.clearContents

 '
 '  Insert a few values
 '
    cells(3, 1) = "foo"
    cells(4, 1) = "bar"
    cells(5, 1) = "foo"
    cells(6, 1) = "baz"
    cells(7, 1) = "baz"
    cells(8, 1) = "foo"

end sub ' }

Github repository about-Excel, path: /functions/countif/count-specific-values.bas

foo occurs three times, bar once and baz twice:

Count occurences of a value

countif can be used to count how many times a given value appears in a range.

Thus, it allows to check which value are unique within a certain range.

In the following example, the result of countif shows how many times the value left of the countif is found in A1:A6.

option explicit

sub main() ' {

    testData

    range(cells(1,2), cells(6,2)).formulaR1C1 = "=countif(r1c1:r6c1,rc[-1])"

end sub ' }

sub testData() ' {

 '
 '  Clear active sheet's data:
 '
    activeSheet.usedRange.clearContents

 '
 '  Insert a few values
 '
    cells(1, 1) = "foo"
    cells(2, 1) = "bar"
    cells(3, 1) = "foo"
    cells(4, 1) = "baz"
    cells(5, 1) = "baz"
    cells(6, 1) = "foo"
end sub ' }

Github repository about-Excel, path: /functions/countif/count-occurences.bas

Compare with the Excel menus

Excel function: countif

Count specific values

Count occurences of a value

See also