SUM Formula??

**cbell25** · 04-13-2012, 10:09 AM

I'm trying to create a formula on excel where it will give the SUM of every 4th cell in a row. I have tried using just a simple =SUM() formula and clicking on each cell indivdually but this comes back with an error due to the number of individual functions (there are 47 in total). Is there an easy way of doing this?

**Andrew-R** · 04-13-2012, 10:16 AM

Maybe something like this:

=SUMPRODUCT(A1:Z1,--(MOD(COLUMN(A1:Z1),4)=0))

**cbell25** · 04-13-2012, 11:02 AM

Hi Andrew,

Thanks for the response. This doesn't appear to have given the desired results though. It still seems to take into account every cell in the row.

Any more suggestions would be greatly appreciated.

**Søren Larsen** · 04-13-2012, 11:13 AM

Try this:

=SUM(--(MOD(COLUMN(A1:Z1),4)=MOD(COLUMN($A$1),4))*(A1:Z1)) and confirm with Ctrl+Shift+Enter

And make the array in the formula fit your data, the COLUMN($A$1) part is the first cell from the left of your array.

**Fotis1991** · 04-13-2012, 11:19 AM

Hi

My suggestion, is to take a look again, Andrew's formula.It's works for me.

**FDibbins** · 04-13-2012, 11:26 AM

coould you add a helper column that puts 1 (or anything you want) for every 4th row, and then ise a sumif() based on the helper col?

**Andrew-R** · 04-13-2012, 11:58 AM

Cbell,

Can you post a sample workbook so we can see your data, please?

**Marcol** · 04-13-2012, 11:58 AM

Andrews' solution is correct, but only allows for 26 columns begining with Column A, i.e. Column D is the first column counted.

This should allow for as many columns as you need.
=SUMPRODUCT(A2:INDEX(2:2,1,MATCH(99^99,2:2,1)),--(MOD(COLUMN(A2:INDEX(2:2,1,MATCH(99^99,2:2,1))),4)=0))

If Column A isn't the first column with data, then change =0 to adjust the starting point.

**cbell25** · 04-13-2012, 12:16 PM

Hi, still struggling! Here is a sample of the data (obviously excluding anything sesitive). I'm looking to put the formula into cell HQ3 taking into account every 4th cell starting with A3 and ending with HM3. I would then want to copy this across into cells HR3, HS3 and HT3.