Count values in groups

Question

I have a table with student IDs separated in groups. I need a handy way to count the total number of students in each group and populate it after the last row of each group (marked with ??)

Currently I just enter =COUNT() and then manually figure out the top and bottom borders of the range for each group. Not convenient at all.

I was thinking that a possible solution could be one of the following:

1. A some kind of pivot table permutation. I failed on this one.
2. Excel Data->Outline->Subtotals functions. Again, fail. It keeps creating new rows in my table.
3. A universal formula that can be pasted into each ?? cell. Not the most graceful solution, but still would do.
4. A macro. As a last remedy if nothing else works.

Answer 1

The following steps will calculate the subtotals while preserving the structuring and formatting of your worksheet.

1. Put this formula in cell C1 and copy the formula down the column:

   =IF(NOT(ISERROR(SEARCH("Total",A1))),COUNTA(INDIRECT("B"&MATCH(LEFT(A1,LEN(A1)-7),A:A,0)+1&".B"&(MATCH(A1,A:A,0)+1))),IF(B1="","",B1))
   

2. Apply a conditional format to cell C1 with the formula rule =(MOD(ROW(C1),2)=0) and blue fill to match the shading on the other rows. Copy the format down the column using Paste Special Format.

3. Either hide column B, or copy the values in column C to column B using Paste Special Values and hide Column C. If you decide to copy the values to column B, you won't need to set the conditional formats.

Here is what the formula does:

• First, check whether the formula's row is a Total row, by searching the cell in column A of the row for the word "Total," using the SEARCH function.

  If the word "Total" is found:

  Determine the range in the worksheet of the student IDs for the group for that total row:

  a) Identify the rows in which the words "GroupX" and "GroupX Total" are found by using the MATCH function. With that, you know that the IDs for the group are in a range that starts at, say, row x and ends at row y.

  b) With the starting and ending row numbers, construct the address range in which the IDs lie, which has to be the string "B" + (row x) + "." + "B" + (row y).

  c) Turn the string into a range reference that can actually used in a formula using the INDIRECT function.

  Count the number of students in the group using the COUNTA function and the range, and show that as the formula's result.

  If the word "Total" is not found

  Check whether the cell in column B is empty

  a) If it is empty, show a blank as the formula's result

  b) if it is not empty, it must be a student ID, so show the ID as the formula's result.

Answer 2

Add a column (I usually add it to the LEFT of the existing matrix) where you enter a formula from row 2 onwards that fills the blanks in the old column A. Then the old matrix including your new column can be used in a pivot.

1. So Insert a column left of your matrix, this is column A now. Put a header in Cell A1, for example "Group Name1"

2. Enter the following formula in cell B2 and extend it to the end: =IF(B2="",A1,B2) This way your blanks will be filled.

3. Now apply a pivot on this matrix and there you are.

Maybe not the nicest looking solution, but its quick and works well.