Create summary every 10 rows-CodePudding

I want to crate summary from every 10 rows.

|Col-A    |Col-B          |Col-C   |Col-D |Col-E|Col-F|Col-G |Col-H|
====================================================================
|InvoiceNo|TotalAmount Tax|TotalTax|PartNo|Qty  |Price|Amount|Tax  |
--------------------------------------------------------------------
|Invoice-A|12540          |1140    |0001  |3    |100  |300   |30   |
|Invoice-A|12540          |1140    |0003  |6    |300  |1800  |180  |
|Invoice-A|12540          |1140    |0002  |5    |200  |1000  |100  |
|etc...   |12540          |1140    |xxxx  |x    |xxx  |xxxx  |xxx  | --10th row
|Invoice-B|xxxxx          |xxxx    |xxxx  |x    |xxx  |xxxx  |xxx  | --11th row

Summary

I have for next looping code like this

 'Total amount Total tax
    rw = 10
    For j = 2 To lastRow Step 10
      sht.Range("B" & lastRow).Formula = "=SUM(H" & t & ":H" & rw & ") SUM(G" & j & ":G" & rw & ")"
        rw = rw   10
    Next j

   'Tax
    rw = 10
    For t = 2 To lastRow Step 10
      sht.Range("C" & lastRow).Formula = "=SUM(H" & t & ":H" & rw & ")"
        rw = rw   10
    Next t

Any idea to create summary of 10 rows of each invoice number?

CodePudding user response：

If you want to sum up values by respective invoice numbers, you can create a list with UNIQUE invoice numbers and then SUMIF against these unique values (directly as cell function w/o VBA)

If you want to sum up every 10 rows, you can do the same by adding a index column calling the first 10 rows 1, the next 10 rows 2, etc. Then do the same as above by SUMIF against UNIQUE index number instead.

Or set the rows of the index column repeatly from 1 .. 10, 1 .. 10, etc. (summing 1st with 11th row, 2nd with 12th, etc.)