Subtotal of every nth row of Filtered excel data

ootkhopdi

Board Regular
Joined
Sep 30, 2013
Messages
68
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
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
2,681
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Example: if you filtered for example row 7 then you sum row 4,10,13, .... or 4, 8, 11, ....
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
2,681
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
This Macro Sum Filtered Data Every N Row
VBA Code:
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

Well-known Member
Joined
Oct 22, 2012
Messages
2,681
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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.
VBA Code:
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

Well-known Member
Joined
Oct 22, 2012
Messages
2,681
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

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.
Excel Formula:
MOD(ROW(),3)
And If you want sum column C Use this:
Excel Formula:
=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

Board Regular
Joined
Sep 30, 2013
Messages
68
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

Well-known Member
Joined
Oct 22, 2012
Messages
2,681
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Oct 22, 2012
Messages
2,681
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
This is Example:

Mail Generator2.xlsm
ABCDEFGHI
1
2
3NameIDValueMOD FormulaSum Visible Cells
4Employee AMD21000012144Rows 4,7,10,…
5Employee BMD21000024246Rows 5,8,11,…
6Employee CMD21000038037Rows 6,9,12,…
7Employee AMD210000171
8Employee BMD210000262
9Employee CMD210000350
10Employee AMD210000111
11Employee BMD210000292
12Employee CMD210000310
13Employee AMD210000161
14Employee BMD210000272
15Employee CMD210000360
16Employee AMD210000171
17Employee BMD210000242
18Employee CMD210000360
19Employee AMD210000181
20Employee BMD210000242
21Employee CMD210000330
22Employee AMD210000111
23Employee BMD210000242
24Employee CMD210000310
25Employee AMD210000171
26Employee BMD210000232
27Employee CMD210000320
28Employee AMD210000151
29Employee BMD210000252
30Employee CMD210000350
73
PL2002
Cell Formulas
RangeFormula
G4:G6G4=SUMPRODUCT(($E$4:$E$400=$E4)*(SUBTOTAL(109,OFFSET($C$4,ROW($C$4:$C$400)-MIN(ROW($C$4:$C$400)),0))))
E4:E30E4=MOD(ROW(),3)
 

Forum statistics

Threads
1,148,140
Messages
5,745,034
Members
423,917
Latest member
Frank1931

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top