« Office Automation Ltd.
More Checkboxes on Worksheets »

Counting Blocks

20th October 2004, 09:08 am by Dick Kusleika

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

Countblocks1

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.

Countblocks2

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.

Category: Worksheet Functions  |  Comment (RSS)

5 Comments

  1. vkd:

    Damn, you are clever, Dick !

    20 October 2004, 9:28 am

  2. Andy Pope:

    Try this for a non array version of the formula.

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

    20 October 2004, 10:24 am

  3. Andrew:

    Cool!

    20 October 2004, 12:39 pm

  4. Ian:

    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.

    21 October 2004, 6:11 am

  5. frank:

    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.

    25 October 2004, 1:47 pm

Certain comments are subject to moderation and may not appear immediately. You can use HTML tags in your comment. If you include a greater-than or less-than sign or anything else that could be interpreted as HTML, your comment won't look nice. You need to escape those characters. To post VBA code in your comment, use [VB] tags, like this: [VB]Code goes here[/VB].

Leave a comment