Summing thousands of skipping rows of data

Try inserting a column or finding a blank one beside the data and put a flag on every row you want to sum. You might be able to do this with an if formula. then use sumif to add the flagged rows together or use a pivot table with the flag selected in the page field. Sumif does like it when there is text amongst the cells being summed

Ian,
If you are looking to sum one or more line items in a group of line items, the group of line items being all the charges for one client and do this for each client on the sheet?
Try looking on your sheet for some data which is in the row of the col. you want to sum and not in the row's you don't. Try a product code, name, ect.
The way off to the Right of your sheet, in a col. you will not print or see without looking for it, enter:

=IF('sheet1'!product>0,the col.row of the value you want,0), like =IF(c6>0,e6,0), then at the bottom of this new col. =SUM(AA6:AA106), then in this case your total will be in "AA107" so you can display this total in any cell on your sheet by: =AA107 whereever.

You could also write a macro to work with Ctrl+ hot key or a object button on your sheet. Thee ways come to mind now and I am sure their are some other ways to do it. you can increase the row number by 11 to populate an array then sum the array or use a For Next Loop counter to increase a variable with a population test to end the loop or nest two if tests one tests the 11th row by dividing its row number by 11 and the other tests if the result is 1,2,3... if it is it increases a variable which is initialized with your first row value by the row number value of the col. which meets the nested IF test.

If this is the way you want to go repost and on Tuseday when I am back in the office I will try to code something out?

Yeah, I think I'd like to go the macro route, except that I have absolutely no clue about VBA. Any help you are willing to offer would be great

Ian

Ian

I couldn't resist to meet "your expectation."

In E1 enter: 11 [ Name this cell Nth via the Name Box ]
In E2 enter: $C$5 [ the start cell ]
In E3 enter: $C$49 [ the end cell ]
In E4 enter: =ADDRESS(ROW(INDIRECT(E2))+1,COLUMN(INDIRECT(E2)))&":"&E3

In F1 array-enter: =INDIRECT(E2)+SUM((ISNUMBER(INDIRECT(E4)))*(MOD(ROW(INDIRECT(E4)),Nth)-ROW(INDIRECT(E2))=0)*(INDIRECT(E4)))

Or, less fancy -- where you can skip entering things into the range E1:E4, array-enter

=C5+SUM((ISNUMBER(C6:C49)*(MOD(ROW(C6:C49),11)-5=0)*(C6:C49)))

Aladin

========================

Try Array formula
(Ctrl + Shift + Enter)

=SUM(IF(MOD(ROW(C5:C2000)-ROW(C5),11)=0,C5:C2000))

Assumes range is C5:C2000

See Mrexcel Tip on CSE

Ivan

Beautiful! You guys are geniuses. Thanks so much for your help.

Ian