article

Calculate nth Row in Excel

Email
Submitted on: 2/17/2015 11:35:00 PM
By: Todd Benson (from psc cd)  
Level: Intermediate
User Rating: By 2 Users
Compatibility: VBA MS Excel
Views: 1264
 
     Provide understanding of how to calculate every nth row (e.g. every other row, every 35th row, etc.) in Excel, even when the data does not begin in cell A1.

This article has accompanying files
 
				

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,000-plus 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.

winzip iconDownload article

Note: Due to the size or complexity of this submission, the author has submitted it as a .zip file to shorten your download time. Afterdownloading it, you will need a program like Winzip to decompress it.Virus note:All files are scanned once-a-day by Planet Source Code for viruses, but new viruses come out every day, so no prevention program can catch 100% of them. For your own safety, please:
  1. Re-scan downloaded files using your personal virus checker before using it.
  2. NEVER, EVER run compiled files (.exe's, .ocx's, .dll's etc.)--only run source code.
  3. Scan the source code with Minnow's Project Scanner

If you don't have a virus scanner, you can get one at many places on the net including:McAfee.com


Other 2 submission(s) by this author

 


Report Bad Submission
Use this form to tell us if this entry should be deleted (i.e contains no code, is a virus, etc.).
This submission should be removed because:

Your Vote

What do you think of this article (in the Intermediate category)?
(The article with your highest vote will win this month's coding contest!)
Excellent  Good  Average  Below Average  Poor (See voting log ...)
 

Other User Comments


 There are no comments on this submission.
 

Add Your Feedback
Your feedback will be posted below and an email sent to the author. Please remember that the author was kind enough to share this with you, so any criticisms must be stated politely, or they will be deleted. (For feedback not related to this particular article, please click here instead.)
 

To post feedback, first please login.