Sometimes, requested data is delivered to me in Excel, but not in tabular
format. Instead, it is formatted like a report where subtotals are already
calculated and the values are inserted. The grand total (sum of the subtotals),
however, is not included.
The format is like the following:
Group1 
Detail1 
ValueA 
Group1 
Detail2 
ValueB 
Group1 
Detail3 
ValueC 
Group1 
Detail4 
ValueD 
Group1 
Subtotal1 
SubtotalValue1 
Group2 
Detail1 
ValueE 
Group2 
Detail2 
ValueF 
Group2 
Detail3 
ValueG 
Group2 
Detail4 
ValueH 
Group2 
Subtotal2 
SubtotalValue2 
As you can see there are subtotals on lines 5 and 10. I want to sum them into
a grand total. Simply using the =Sum() function on the column will not get me
the result I want since it will likely sum the whole column, including the
subtotals, or require me to loop through every fifth line to create a string to
populate the =Sum() function (ex., =Sum(E5,E10,E15,En)). Why? Because the cell
in which I place that example can throw an error due to the length of the
formula string (imagine if I had 1,000plus groups)
So what does a person do? A mix of Excel functions will allow me to do this.
Functions like SumProduct(), Mod() and Row().
I will provide an example that has data that doesn’t start in cell A1, but
somewhere else on the sheet, like C3. That way you can learn the flexibility of
this solution. I also chose an example that requires me to use every fifth row.
I freely admit that you can find this solution elsewhere on the net. However, it
is difficult to find. Therefore, I thought I’d help out the community by
providing it here.
The attached Excel example follows the example above with one difference: There
are four groups instead of two.
THE SETUP: For attached Excel example, my value data that I want to sum starts
in Column E, Row 3. It ends in Column E, Row 22. Each Group has four values in
successive rows followed by a subtotal of the group on the fifth row. That
equals four subtotals (E7, E12, E17 and E22).
THE SOLUTION: I want the grand total to appear after that last subtotal. The
last subtotal is located at E22. Therefore, the grand total will appear in E23.
The formula in E23 is: =SUMPRODUCT($E$3:$E$22,IF(MOD(ROW($E$3:$E$22)2,5),0,1)).
When entered manually, before you leave the cell, be sure to use the keyboard
combination: <CTRL>+<SHIFT>+<ENTER>. Otherwise, this array calculation will not
work. In VB, the code to enter is
oSheet.Cells(lastRow, lngCol).FormulaArray = _
SUMPRODUCT($E$3:$E$22,IF(MOD(ROW($E$3:$E$22)2,5),0,1))
where oSheet is a variable for the Excel Sheet Object.
THE EXPLANATION: Yep, you’ve seen this MOD() function before. Many use it to
color every nth row in Excel. In my example, we sum every fifth row (see the “5”
in the MOD() function?). Let’s break this formula down for better understanding:
SUMPRODUCT = A function that sums the products resulting from multiplying values
from one array with corresponding values from a second array. This is seen as
SUMPRODUCT(array1, array2). In the example, the first array is declared as
$E$3:$E$22. But please remember that I only want every fifth row. That is why
the second array in the example uses the MOD() function with 5 as the divisor.
MOD = The MOD() function, nested in the IF() function, answers the question for
the same array ROW($E$3:$E$22): ‘Is this row NOT one of the every fifth row?’
Confusing logic in that question, eh? If the answer is true (“True, it is NOT a
fifth row) then it multiples the corresponding number from the first array by
the value of the IF()’s truepart, or 0. Otherwise, because it is a fifth row
(“False, it is a fifth row), the value from the first array is multiplied by the
IF()’s falsepart, or 1.
Do you recall that I mentioned I started the data on row three ($E$3)? Great!
Well, the MOD() function needs to take that into account for it to work
properly. In this case, there happen to be two rows (row 1 and row 2) above our
starting row. When you look at the MOD() function in the example formula you can
see I accounted for this by including “2” following the array.
Finally, SUMPRODUCT sums the products of all those calculations (hence, the name
of the function).
EASY EXAMPLE: Let’s use the sample I provided at the top of this article.
Find the products:
ValueA 
3 
Not a fifth row, so multiply by 0.
Product equals 
0 
ValueB 
2 
Not a fifth row, so multiply by 0.
Product equals 
0 
ValueC 
2 
Not a fifth row, so multiply by 0.
Product equals 
0 
ValueD 
6 
Not a fifth row, so multiply by 0.
Product equals 
0 
Subtotal1 
13 
This is a fifth row, so multiply by 1
to equal 
13 
ValueE 
4 
Not a fifth row, so multiply by 0.
Product equals 
0 
ValueF 
1 
Not a fifth row, so multiply by 0.
Product equals 
0 
ValueG 
3 
Not a fifth row, so multiply by 0.
Product equals 
0 
ValueH 
3 
Not a fifth row, so multiply by 0.
Product equals 
0 
Subtotal2 
11 
This is a fifth row, so multiply by 1
to equal 
11 
Grand Total 
** 
Sum the products: (0+0+0+0+13+0+0+0+0+11) = 
24 
** Here the SUMPRODUCT() formula would be inserted and the result would show
24.
