Counting 3D Blocks

Regular reader and commenter, Frank, took my Counting Blocks post and extended it for three dimensions. In Counting Blocks, I took a row of letters, and by offsetting an array formula, only counted groups of those letters. That is, contiguous letters were counted as one.

In 3D, a block would be defined as no ‘S’ to the right, no ‘S’ down, and no ‘S’ in the same position on the next sheet. Assume we have four sheets, each with a 4×4 range of letters.

five excel ranges

For brevity in the formulas, each range is named. Sheet1’s range is “one”, Sheet2’s range is “two”, and so on. The formula for Sheet1 is:

=SUMPRODUCT((one=”s”)*(OFFSET(one,0,1)<>”s”)*(OFFSET(one,1,0)<>”s”)*(two<>”s”))

That translates into: Count the cell if it contains an “S”, the cell to the right (

OFFSET(one,0,1)

) doesn’t contain an “S”, the cell below (

OFFSET(one,1,0)

) doesn’t contain an “S”, and the next sheet (“two”) doesn’t contain an “S”. Just like I had to make sure that there was a blank cell immediately to the right of my linear count, Frank had to add a fifth sheet that was blank to demarcate the end of the 3D block. So in the formula on Sheet4,

=SUMPRODUCT((four=”s”)*(OFFSET(four,0,1)<>”s”)*(OFFSET(four,1,0)<>”s”)*(five<>”s”))

the (five<>"s") will always be true. Finally, add up all the SUMPRODUCT formulas and you have your block count for the whole cube.

=SUM(Sheet1:Sheet4!A6)

Nice work Frank. Now for your next assignment, count blocks where a block would be defined as above except that when the range of letters on the next sheet aren’t in the same shape as the current sheet, it would be considered two blocks. For example,

four excel ranges

four excel ranges

These two should both count two blocks. My head hurts just thinking about it.

Posted in Uncategorized

2 thoughts on “Counting 3D Blocks

  1. Thanks, Dick, for having posted about this terrific topic, yet if there are no other comments than my own, I’ll take the blame.

    “Frankly, Frank, did you really think that any right-minded Excel user would start counting blocks in space?”

    About the assignment, maybe I would change its requirement into finding a way to count groups of contiguous cells in a given space. Even in 2D, this seems to be a dounting task!

    Anyway, give me a couple of years to get my head around it, and if anybody came up with a solution, he or she would deserve the MS XL IP title: Master Solver of Insane Excel Problems.

  2. I found a way to count groups of contiguous cells in a given 2D space – 3D would be no problem if one used several worksheets.

    As long as cells have at least one side in common, they belong to the same group. Cells without any neighbors count as one. So in Dick’s second and third picture, we have one block in both situations.


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

Leave a Reply

Your email address will not be published.