yogi_Count Items In Dynamic Ranges Between Dynamic Row Markers

Google Spreadsheet   Post  #2128

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Mar-14-2017

question by Christopher Elbich:

https://productforums.google.com/forum/#!topic/docs/SBW_QsiKhGA;context-place=forum/docs
counting items in dynamic ranges between dynamic row markers

This one might be a real challenge, but may be easy with a query function. I'm at my wit's end. Please help.

See the sample sheet here.

I am trying to count blank cells within dynamic ranges and with dynamic labels. My gut tells me this is a job for query. I just don't know the syntax very well.

Consider the sheet contains a number of dynamic user-defined ranges. The top row of each range is marked by an underscore ("_*") before the range's label. The end of the whole user-defined area is marked by a double underscore ("__*"). Users may routinely add or subtract rows within the ranges or even create or delete entire labeled sections. Therefore, the tabulation under "__Counts" must automatically account for these changes. Arrayformula would typically do the trick, but it doesn't work with indirect or offset, so I can't make it respond to changes in the range.

I am a relatively advanced user and I have tried various combinations of match, countblank, indirect, offset, or substituting arrays for the physical range via index or filter. I want to avoid scripts, custom functions, or helper sheets or columns. The ultimate question is making the formula respond better to changes in the range and content, especially if entire sections are created or deleted. The "WhatIf" sheet illustrates the kinds of dynamic scenarios to expect.

The sample sheet shows how I want the tally area to respond as users create or delete rows or ranges. I made countblank functions in the B column to illustrate the desired end result. I just can't use countblank with arrayformula and a dynamic range.

BONUS QUESTION:

The third sheet contains more rows at the bottom that tally like a countif function. I want them to also react dynamically to any changes above. They don't necessarily need to be sensitive to the sections (they can include the entire user-defined range).

Good luck!