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.
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:
That translates into: Count the cell if it contains an “S”, the cell to the right (
) doesn’t contain an “S”, the cell below (
) 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,
(five<>"s") will always be true. Finally, add up all the SUMPRODUCT formulas and you have your block count for the whole cube.
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,
These two should both count two blocks. My head hurts just thinking about it.