# 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. vkd says:

Damn, you are clever, Dick !

2. Andy Pope says:

Try this for a non array version of the formula.

=SUMPRODUCT((A2:H2=”S”)*(B2:I2

3. Andrew says:

Cool!

4. Ian says:

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.

5. frank says:

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

={SUM((A2:G7=”s”)*(B2:H7<>”s”)*(A3:G8<>”s”))}.

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

={SUM((Sheet1:Sheet6!A2=”s”)*(Sheet2:Sheet7!A2<>”s”)},

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

6. Kevin Butler says:

Will this work if count contiguous numeric values?
For example all occurences > 72 if contiguous.
Thanks.

7. Dick Kusleika says: =SUM((A1:A12>72)*(A2:A13<=72))

Don’t forget to enter as an array formula – control+shift+enter, not just enter.

8. frank says:

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

9. Dick Kusleika says:
`=SUM(--(((A2:H2="S")+(B2:I2="S"))=2))+1`

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