SUMPRODUCT on Non-Contiguous Cells

[EliHolle]

Hi!

I'm trying to make a formula for the whole spreadsheet that adds up certain cells.
The range of cells I want to add up start from C8 and then I want to add up every 12th row up to cell C596. Like {C8, C20, C32, C44 ... C596}

I've tried =SUMPRODUCT((MOD((ROW(C8:C596)-8),12)=0)*(C8:C596))
But it didn't work and what *(C8:C596) does I don't really understand.

I'd be glad to hear how I could get it done, either by improving this formula or by a completely different way.
Thanks!

Eli

[Cutter]

Is this what you're trying for:

=SUMPRODUCT((MOD(ROW(C8:C596)+4,12)=0)*(C8:C596))

[NBVC]

That formula looks like it should work, unless you have text in any of those non-contiguous cells in the range. Then you would get the #VALUE! error.

the first part of the formula produces an array of TRUE or FALSE to mark every 12 entry... the multiplication of the entire array is basically multiplying each TRUE and FALSE by each corresponding value in the range... a TRUE multiplied by a number produces that number, a FALSE multiplied by a number produces 0 so then the sumproduct adds these all up...to give result.

If you have a text value in any of those cells that match up to a TRUE, you will get

If you have text in there, try:

=SUMPRODUCT(--(MOD((ROW(C8:C596)-8),12)=0),(C8:C596))

[EliHolle]

Cutter, NBVC

Thank you for your fast aswers and sorry for my late repy.

The formula with (-- worked!! I had some text in the cells in this range and indeed got the #value error.

I'm really amazed by your fast and accurate answers. Many, many thanks!!

[Nitesh Shah]

What does the 8 stand for in the formula???

[NBVC]

subtracting 8 from each ROW in C8:C596, so the count will actually start from 0 on...