Adding in increments

steven21 · #1 01-25-2007, 05:00 PM

I am hoping someone could help me. I am trying to figure out a formula that will add the 5th cell in each row of a Spreadsheet I have, currently I have =J5+O5+T5+Z5+...+IO5, but I am needing to add lots more values and I do not want to have to keep updating the formula.

I have asked a few people I know but no one can seem to help.

Is there an IF statement I can use and specify the cell I want it to go upto?

Please help

**VBA Noob** · #2 01-25-2007, 05:03 PM

Hi,

This site might help

http://www.cpearson.com/excel/excelF.htm#SumEveryNth

VBA Noob

**daddylonglegs** · #3 01-25-2007, 05:12 PM

Not sure about your example.....

=J5+O5+T5+Z5+...+IO5

wouldn't it be Y5.....and IP5

....but you could do it similar to Chip's way but with SUMPRODUCT, thus avoiding CSE.....i.e.

=SUMPRODUCT(--(MOD(COLUMN(J5:IV5)-COLUMN(J5),5)=0),J5:IV5)

steven21 · #4 01-25-2007, 06:11 PM

Quote:

Originally Posted by daddylonglegs

Not sure about your example.....

=J5+O5+T5+Z5+...+IO5

wouldn't it be Y5.....and IP5

....but you could do it similar to Chip's way but with SUMPRODUCT, thus avoiding CSE.....i.e.

=SUMPRODUCT(--(MOD(COLUMN(J5:IV5)-COLUMN(J5),5)=0),J5:IV5)

I have given both a go and I either get 0 or 224, not sure what I am doing wrong. the cells I want to add are defintely J5, O5, T5 and so on, so maybe it is not going up in 5

steven21 · #5 01-25-2007, 06:14 PM

Is it ok if I can ask someone to have a look at it? I have uploaded it to a zip file

**VBA Noob** · #6 01-25-2007, 06:48 PM

You have to sort you table out

E.g delete Column X and AM. Didn't check past that.

Formula works just your data not consistant

VBA Noob

steven21 · #7 01-26-2007, 09:53 AM

I have deleted the blank columns but I am still not sure if its correct - for example I know cell G32 should be 2, but its showing 0.

=SUMPRODUCT(--(MOD(COLUMN(J5:IO5)-COLUMN(J5),5)=0),J5:IO5)

is the formula

**VBA Noob** · #8 01-26-2007, 10:28 AM

Data layout still wrong

Column CU should be GP but is G.

Will work if you check the data

Enter a blank row and add this formula in I2 and drag across

=IF(MOD(COLUMN()-8,5)=0,5,(MOD(COLUMN()-8,5)))

G should always equal 2

VBA Noob

**daddylonglegs** · #9 01-26-2007, 10:33 AM

Rather than trying to sum every 5th column it should be easier to use SUMIF based on your column headers

I’m not sure exactly which formulas you’re referring to but try this formula in C5 on u19’s worksheet

=SUMIF($I$4:$IO$4,C$4,$I5:$IO5)

copy across to column G and then down

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread:

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)