Subtotal of every nth row of Filtered excel data

[ootkhopdi]

Hi Everyone,,
Please tell me how sum of every nth row in filtered data,
i mean if i filter my data in excel from row 4 to row 400, i want to sum every 3rd row after data, in row 401, (Sum of Row 4,7,10,....), in row 402,(Sum of Row 5,8,11.......) in row 403( Sum of row 5,9,12,......) Please tell me how can do it..


Thanks in advance

 

[maabadi]

Example: if you filtered for example row 7 then you sum row 4,10,13, .... or 4, 8, 11, ....

 

[maabadi]

This Macro Sum Filtered Data Every N Row

Sub SumV2()
Application.ScreenUpdating = False
Dim MyRange As Range, Lr1 As Long, Lc As Long
Dim i As Long, a As Long, j As Long, Lr2 As Long, SumV As Double
Dim b As Long, SumRange As Range, N As Long, MD As Long

Set SumRange = Application.InputBox(prompt:="Sum range", Type:=8)
N = Application.InputBox(prompt:="Select Every N Row", Type:=1)
MD = Application.InputBox(prompt:="Reminder of Division To N", Type:=1)
a = SumRange.CurrentRegion.Row
b = SumRange.CurrentRegion.Column
Lr1 = Cells(Rows.Count, b).End(xlUp).Row
Lc = Cells(a, Columns.Count).End(xlToLeft).Column
Set MyRange = Range(Cells(a, b), Cells(Lr1, Lc + 2))

For i = a To Lr1
Cells(i, Lc + 2).Value = i Mod N
Next i
MyRange.SpecialCells(xlCellTypeVisible).Copy
Cells(a, Lc + 5).PasteSpecial (xlPasteAll)
Lr2 = Cells(Rows.Count, Lc + 5).End(xlUp).Row

SumV = Application.WorksheetFunction.SumIf(Range(Cells(a, 2 * Lc + 6), Cells(Lr2, 2 * Lc + 6)), MD, Range(Cells(a, Lc + 6), Cells(Lr2, Lc + 6)))
Cells(Lr1 + MD + 2, SumRange.Column).Value = SumV
Range(Cells(1, Lc + 1), Cells(Lr1, 2 * Lc + 6)).ClearContents
Application.ScreenUpdating = True
End Sub

 

[maabadi]

This is Second Version Without InputBox, if you want & Like inputBoxes then Remove ' at the first of two Green Line and Add ' to the two Next Line To can Select Range and Every nth Row.

Sub SumV2()
Application.ScreenUpdating = False
Dim MyRange As Range, Lr1 As Long, Lc As Long
Dim i As Long, a As Long, j As Long, Lr2 As Long, SumV As Double
Dim b As Long, SumRange As Range, N As Long, MD As Long

'Set SumRange = Application.InputBox(prompt:="Sum range", Type:=8)
'N = Application.InputBox(prompt:="Select Every N Row", Type:=1)

Set SumRange = Range("B2:B400")
N = 3
a = SumRange.CurrentRegion.Row
b = SumRange.CurrentRegion.Column
Lr1 = Cells(Rows.Count, b).End(xlUp).Row
Lc = Cells(a, Columns.Count).End(xlToLeft).Column
Set MyRange = Range(Cells(a, b), Cells(Lr1, Lc + 2))

For i = a To Lr1
Cells(i, Lc + 2).Value = i Mod N
Next i
MyRange.SpecialCells(xlCellTypeVisible).Copy
Cells(a, Lc + 5).PasteSpecial (xlPasteAll)
Lr2 = Cells(Rows.Count, Lc + 5).End(xlUp).Row
For i = 0 To N - 1
SumV = Application.WorksheetFunction.SumIf(Range(Cells(a, 2 * Lc + 6), Cells(Lr2, 2 * Lc + 6)), i, Range(Cells(a, Lc + 6), Cells(Lr2, Lc + 6)))
Cells(Lr1 + i + 2, SumRange.Column).Value = SumV
Next i
Range(Cells(1, Lc + 1), Cells(Lr1, 2 * Lc + 6)).ClearContents
Application.ScreenUpdating = True
End Sub

 

[maabadi]

This is Formula Version.
1. Add one Helper Column after last column ( I suppose it is column H) of your table and add this formula to them & Drag it down.

MOD(ROW(),3)

And If you want sum column C Use this:

=SUMPRODUCT(($H$3:$H$400=$H3)*(SUBTOTAL(109,OFFSET($C$3,ROW($C$3:$C$400)-MIN(ROW($C$3:$C$400)),0))))

And drag it until you have 3 number.

 

[ootkhopdi]

Thanks for Reply ,but these replies not solve my problem.

i want to clear more my problem.

i want to sum ,every nth row of Only filtered data(Visible Rows) , means
if in filter my data and show only row 4 to row 30, than in row 401 ,subtotal only row 4,7,10,13,16,19,22,25,28 , in row 402, subtotal row 5,8,11,14,17,18,20,23,26,29 and in row 403, subtotal row 6,9,12,15,18,21,24,27 and row 30..

So Please tell me how can i do it..

 

[maabadi]

Are you test this. these are exactly what you want.
Please upload on example file with XL2BB ADDIN(preferable) or upload at free hosting site e.g. www.dropbox.com , GoogleDrive or OneDrive and insert link here, to I I I show you result with these formula and Macro.

 

[maabadi]

This is Example:

Mail Generator2.xlsm
	A	B	C	D	E	F	G	H	I
1
2
3	Name	ID	Value		MOD Formula		Sum Visible Cells
4	Employee A	MD2100001	2		1		44		Rows 4,7,10,…
5	Employee B	MD2100002	4		2		46		Rows 5,8,11,…
6	Employee C	MD2100003	8		0		37		Rows 6,9,12,…
7	Employee A	MD2100001	7		1
8	Employee B	MD2100002	6		2
9	Employee C	MD2100003	5		0
10	Employee A	MD2100001	1		1
11	Employee B	MD2100002	9		2
12	Employee C	MD2100003	1		0
13	Employee A	MD2100001	6		1
14	Employee B	MD2100002	7		2
15	Employee C	MD2100003	6		0
16	Employee A	MD2100001	7		1
17	Employee B	MD2100002	4		2
18	Employee C	MD2100003	6		0
19	Employee A	MD2100001	8		1
20	Employee B	MD2100002	4		2
21	Employee C	MD2100003	3		0
22	Employee A	MD2100001	1		1
23	Employee B	MD2100002	4		2
24	Employee C	MD2100003	1		0
25	Employee A	MD2100001	7		1
26	Employee B	MD2100002	3		2
27	Employee C	MD2100003	2		0
28	Employee A	MD2100001	5		1
29	Employee B	MD2100002	5		2
30	Employee C	MD2100003	5		0
73
PL2002

Cell Formulas
Range	Formula
G4:G6	G4	=SUMPRODUCT(($E$4:$E$400=$E4)*(SUBTOTAL(109,OFFSET($C$4,ROW($C$4:$C$400)-MIN(ROW($C$4:$C$400)),0))))
E4:E30	E4	=MOD(ROW(),3)