MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Summing thousands of skipping rows of data


Posted by Ian on April 20, 2001 5:44 PM

This may seem really stupid, but I'm not very advanced with excel. I am having trouble figuring out a way that I can sum cell C5, and every 11th cell after that, down to a predetermined cell number. It would also be really nice if the formula could detect the fact that there are no more numbers to sum. This formula, or macro i guess is to be used in hundreds of billing forms. Thanks very much for your help

Ian


Posted by Malc on April 20, 2001 8:32 PM

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

Posted by Joe Was on April 20, 2001 10:24 PM


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?

Posted by Ian on April 20, 2001 11:17 PM

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

Posted by Aladin Akyurek on April 21, 2001 1:45 AM

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

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

Posted by Ivan Moala (IFM) on April 21, 2001 3:39 AM

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

Posted by Ian on April 21, 2001 8:45 AM

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

Ian