Thread: SUMPRODUCT on Non-Contiguous Cells

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

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))