0

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.

pnuts
  • 58,317
  • 11
  • 87
  • 139
Kjell Rilbe
  • 1,331
  • 14
  • 39
  • Unmerging would work but ruin the layout. My own answers? I didn't give any...? If I du SUM.IF($A;2014-04-25;$Z) it will return 120 for row 8, as opposed to 360 = sum of rows 8, 10 and 12. That's the problem I want to solve. – Kjell Rilbe Apr 28 '14 at 17:56
  • Oh, you were referring to other questions? I'll check that... I am unable to follow your instructions, possibly due to different Excel version and/or different product language. Mine is Swedish. Can you clarify? – Kjell Rilbe Apr 29 '14 at 15:20

1 Answers1

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.
pnuts
  • 58,317
  • 11
  • 87
  • 139
  • Ah, so in essence you unmerge, fill the cells of each unmerged range with the merged range's original value, color the text of all but one of the cells in each unmerged range to match background. End result: regular tabular format with date repeated for all rows, but colored so only one of the rows actually shows the value. That's a viable workaround, yes (much appreciated!), although not strictly an answer to my question. – Kjell Rilbe Apr 29 '14 at 15:50
  • The color formatting could be conditional so it becomes automatic when I enter new bowling sessions... Nice... – Kjell Rilbe Apr 29 '14 at 15:52