SumProduct of Only Visible Rows

ootkhopdi

Board Regular
Joined
Sep 30, 2013
Messages
68
Hi All

i have a Sheet in which Rows 1 to 3004 having Diffrent values in Column I
total on rows 5 to 3004 in row no 3006
as sum product of Every 3rd Row as
=SUMPRODUCT(I$5:I$3004*(MOD(ROW(I$5:I$3004),3)=0))
with this formula i got sum value of row no. 6,9,12,15.......3000 and 3003
but i want to sumproduct of only these rows which are visible after apply filter
as
Row no.6,9,15,18,24,33,99 and 102 only
other way when i am applying filters in rows 5 to 3003 , then rows which are similar to filter creteria are visible
and i want to sum every nth rows of these filtered rows...

any one can help me...???
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,414
See if this formula does what you need
=SUMPRODUCT(SUBTOTAL(3,OFFSET(I$5:I$3004,ROW(I$5:I$3004)-ROW(I$5),0,1)),I$5:I$3004*(MOD(ROW(I$5:I$3004),3)=0))

Hope this helps

M.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,123,310
Messages
5,600,884
Members
414,413
Latest member
Sinbin

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