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.
Damn, you are clever, Dick !
Try this for a non array version of the formula.
=SUMPRODUCT((A2:H2=”S”)*(B2:I2
Cool!
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.
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.
Will this work if count contiguous numeric values?
For example all occurences > 72 if contiguous.
Thanks.
=SUM((A1:A12>72)*(A2:A13<=72))
Don’t forget to enter as an array formula – control+shift+enter, not just enter.
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