Sum cell range filtered by merged value?

问题:

I have a bowling result sheet in usual format, where each series contains one row for the actual scores and below that the accumulated score so far, incrementing to the right. For each bowling session I have the date and the name of the bowling hall, and for each series the series number. Example showing two bowling sessions (with identical scores because I'm lazy):

  |   A   |   B   |   C   | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z |
 1| Date  | Hall  |Series |   1   |   2   |   3   |   4   |   5   |   6   |   7   |   8   |   9   |  10   |Extra  |Sum|
 2|       |       |   1   | 5 | 4 | 0 | 5 |10 |   |10 |   | 2 | 5 | 5 | 4 | 0 | 5 |10 |   |10 |   | 2 | 5 |       |120|
 3|       |       |       |   9   |  14   |  36   |  53   |  60   |  69   |  74   |  96   |  113  |  120  |       |   |
 4| 140113| CBH   |   2   | 5 | 4 | 0 | 5 |10 |   |10 |   | 2 | 5 | 5 | 4 | 0 | 5 |10 |   |10 |   | 2 | 5 |       |120|
 5|       |       |       |   9   |  14   |  36   |  53   |  60   |  69   |  74   |  96   |  113  |  120  |       |   |
 6|       |       |   3   | 5 | 4 | 0 | 5 |10 |   |10 |   | 2 | 5 | 5 | 4 | 0 | 5 |10 |   |10 |   | 2 | 5 |       |120|
 7|       |       |       |   9   |  14   |  36   |  53   |  60   |  69   |  74   |  96   |  113  |  120  |       |   |
 8|       |       |   1   | 5 | 4 | 0 | 5 |10 |   |10 |   | 2 | 5 | 5 | 4 | 0 | 5 |10 |   |10 |   | 2 | 5 |       |120|
 9|       |       |       |   9   |  14   |  36   |  53   |  60   |  69   |  74   |  96   |  113  |  120  |       |   |
10| 140425| Bowly |   2   | 5 | 4 | 0 | 5 |10 |   |10 |   | 2 | 5 | 5 | 4 | 0 | 5 |10 |   |10 |   | 2 | 5 |       |120|
11|       |       |       |   9   |  14   |  36   |  53   |  60   |  69   |  74   |  96   |  113  |  120  |       |   |
12|       |       |   3   | 5 | 4 | 0 | 5 |10 |   |10 |   | 2 | 5 | 5 | 4 | 0 | 5 |10 |   |10 |   | 2 | 5 |       |120|
13|       |       |       |   9   |  14   |  36   |  53   |  60   |  69   |  74   |  96   |  113  |  120  |       |   |

Now, I'd like to sum the scores for all series from the bowling session on date 140425 (YYMMDD), using a formula.

How would I do that?

Please note that the date is placed in a merged cell covering all rows for that bowling session, i.e. for the first session the merged cells are A2:A7 and for the second session A8:A13. I know I need to reference the first cell in the range to get the value, but how would I do that in a SUM.IF(...) formula, or otherwise find the right cell to reference for each score line?

The number of series per session can vary from one and up.

回答1:

Although not shown as such in the OP, it would appear that A2:A7 and A8:A13 have been merged. Somewhere on SE someone described merged cells along the lines of "a creation of the Devil sent to try us beyond endurance" - all advice I respect says merging is best avoided with vigour. The above is IMO a good example of the disproportionate problems merged cells can cause.

• Unmerge each set of merged cells

Select merged range, HOME > Alignment, -Merge & Center.
Should show required values in the top cell of each range that was merged.

• Fill blanks

Select ColumnA, HOME, Editing, -Find & Select, Go To Special, check Blanks (only), OK.
=, Up, Ctrl+Enter.

• Select for font colour

Select sheet, DATA > Sort & Filter, -Filter.
For ColumnB select (Blanks) (only).
Select ColumnA and apply font colour to match background.

• Proceed with SUMIF formula.