Counting Blocks

Ian wants to count all the S’s in a certain range. That’s easy enough using COUNTIF(), but he also wants to count only blocks of S’s. To illustrate


There are five S’s, but only three blocks of S’s, namely A2, C2:E2, and H2. If you can afford to leave column I blank, you can use an array formula to make that calculation. This formula only counts an S if the next cell is not an S.


By offsetting the ranges used in the array formula, I can compare a cell to its neighbor. Don’t forget Ctl+Shift+Enter to enter and edit array formulas.

Posted in Uncategorized

9 thoughts on “Counting Blocks

  1. This, is Ian who asked this one, we have a phrase locally which is a complement

    ìcuffing hell you cleaver b****dî.

    Fantastic answer and so quick, amazing.

  2. Dick, “Counting blocks” is a beautiful and amazing formula . It also works in two dimensions, like this:


    Ideally the blocks should be blocks, not crosses or L-shapes.

    Some time ago you posted something about summing in 3D. It would be nice if counting blocks could also work in 3D, using something like


    but these formulas don’t seem to work with arrays.

  3. I’m looking for a formula that gives me the length of the longest block.

    e.g. in your example s sss s the longest block has length 3

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.