excel formul HELP PLEASE

hope that you can help in what im sure is a simple question.

I need a formula that for each row picks up, say, every 5th cell across that row?

can anyone help?

Thanks

Hi,

If your data in column A you could try entering this formula in column b to return every fifth cell. Just drag it down

=IF(MOD(ROW(),5)=0,A1,"")



VBA Noob

thanks but not sure if that works with what i am trying to get and dont think i explained fully wot i am trying to get.


In cell A1 i want the sum of E6, (E+5)6, (E+10)6 etc etc; ie the sum of every fifth cell along row A

can this be done?

thanks

Hi,

So A1 in Formula and you want to sum E5, E10 etc.

Enter this in cell A1

=SUM(IF(MOD(ROW(E1:E1000),5)=0,E1:E1000,""))

It's an Array so needs to be entered with Ctrl + Shift + enter

VBA Noob

nearly, In A1 i want to sum E1, J1, O1, T1 etc etc.

i tried adapting your formula with col or column instead of row and also changed the range but just returned #NAME????

any ideas

thanks

Hi,


Another Array (Ctrl + Shift + Enter)

=SUM(IF(MOD(COLUMN(B1:IV1),5)=0,B1:IV1,""))

VBA Noob

thats the one. thank u!!

No problem

VBA Noob

Here's the non array version

=SUMPRODUCT(--(MOD(COLUMN(B1:IV1),5)=0)*(--(B1:IV1)))

VBA Noob

hi again!!

when i type the formula in to test on a random row of numbers it appears to work and ive changed the rows etc to test and all seems fine. but when i copy the formula into my data it doesn't seem to pick up what i thought it would. it either returns 0 or the same figure the cell i copied it from shows which was right for that row.

My first total in Cell A3 should be the sum of every 11th cell on row A


its annoying because i can see the formula working on a row ive just typed random nos into.

any ideas at all?

ive tried the non array too and that doesn't seem to work?

thanks

Originally Posted by pie8ers

hi again!!

when i type the formula in to test on a random row of numbers it appears to work and ive changed the rows etc to test and all seems fine. but when i copy the formula into my data it doesn't seem to pick up what i thought it would. it either returns 0 or the same figure the cell i copied it from shows which was right for that row.

My first total in Cell A3 should be the sum of every 11th cell on row A


its annoying because i can see the formula working on a row ive just typed random nos into.

any ideas at all?

ive tried the non array too and that doesn't seem to work?

thanks

Can you copy the formula and name the cell you copied it to, also the 'test' cell formula and that cell's address.
---