I wouldn't give up so quickly. Your sheets could be improved upon, as they are difficult to follow, but I believe you can obtain the subgroup sums as follows:
In your ACC sheet, add an indexing row so that all columns of any given data set (including the blank column that follows the TP, FWD, DB, RB columns) have the same number. This shows a single formula that will spill to the right to fill all columns, assuming you place it over the 1st TP column...note the row (here I've placed it in row 24...note that the formula is set up to spill for the same number of columns between D:EC, inclusive, so by placing it in D24, the last spilling value will be in EC24):
Then when you compute totals, it appears that you want the 1st entry for every day, so column C values of {1;6;11;16;...} are of interest.
We note that if we divided each of these values by 5 and consider only the remainder, all have a remainder of 1. We use this idea to construct part of the formula for your Overview sheet.
Excel Formula:
MOD(ACC!$C$27:$C$526,5)=1
This will identify only the rows indexes of interest on the ACC sheet.
Now let's say we wanted to create subgroups of the data sets by grouping every 6 together...so I enter a 6 in J5 (you actually want 20 there) and the subgroup data sets appear to the right (these are here for illustration purposes only):
| MrExcel_20220514_Help Please.xlsx |
|---|
|
|---|
| I | J | K | L | M | N | O | P | Q |
|---|
| 3 | Start at dataset | 1 | | | | | | | |
|---|
| 4 | Number of datasets | 26 | | | | | | | |
|---|
| 5 | Number datasets in ea subgroup | 6 | | 1 | 2 | 3 | 4 | 5 | 6 |
|---|
| 6 | Number col ea subgroup | 4 | | 7 | 8 | 9 | 10 | 11 | 12 |
|---|
| 7 | Number subgroups | 5 | | 13 | 14 | 15 | 16 | 17 | 18 |
|---|
| 8 | | | | 19 | 20 | 21 | 22 | 23 | 24 |
|---|
| 9 | | | | 25 | 26 | 27 | 28 | 29 | 30 |
|---|
| 10 | | | | 31 | 32 | 33 | 34 | 35 | 36 |
|---|
| 11 | | | | 37 | 38 | 39 | 40 | 41 | 42 |
|---|
|
|---|
These arrays of data set numbers are created using the SEQUENCE formula shown and then pulled down row by row to create subsequent subgroup dataset numbers. I haven't placed any error checking in this formula, so it is possible to pull the formula down to create data set numbers that do not exist. In any case, this idea is also used in the main Overview formula to identify columns of interest. Specifically, it looks like this:
Excel Formula:
ISNUMBER(MATCH(ACC!$D$24:$EC$24,SEQUENCE(1,$J$5,$J$3+$J$5*(ROWS($5:5)-1)),0)
where we match the data set numbers (which were added in the first step above) to those that need to be included in each subgroup, and where there is a match, a number will be found in the resulting array, and an error code will be found where no matches are found. We then run a logic check on this array to evaluate where we have numbers, and those are the column index positions of interest. There is another more obvious column-matching condition that requires that the text in the Overview table heading (FWD, DB, RB) must also match the column heading in the ACC table.
Putting these ideas together, the Overview table would look like this:
If data are present on the ACC sheet, you shouldn't get any errors, and I haven't placed any error suppression features in the formula just yet. Let me know if this produces expected results. The SEQUENCE function that appears in this main formula could be streamlined somewhat if you do not want the flexibility to make different size subgroups or start the summary with a different data set number (other than 1).
