Issues with SUMIF/MOD(ROW formula

Question

In an Excel 2021 spreadsheet I want to add values in a column that are repeatedly separated by 10 rows each. Now I could use a formula like:

=SUM($I$8+$I$18+$I$28+$I$38+$I$48+$I$58+$I$68)

but that would eventually be too long, given that there are several hundreds of lines.

I did therefore try to apply a formula like:

=SUM(IF(MOD(ROW($I$10:$I80),10)=0,$I$10:$I80,0))

which works fine, but only for the values starting in cell $I$10.

If however I apply the formula starting at e.g. cell $I$8, to get a formula looking like this:

=SUM(IF(MOD(ROW($I$8:$I18),10)=0,$I$8:$I18,0))

the result is already completely false, returning only the value for cell $I$10.

I can't get my head around why this is not working for me.

Any help will be greatly appreciated.

Thanks

Jaydee

Answer 1

=SUM(IF(MOD(ROW($I$10:$I40),10)=0,$I$10:$I40,0))/SUM(--(MOD(ROW($I$10:$I40),10)=0))

Answer 2

Hija HansV,

thanks for your timely response to my question. Your answer was bang on the money and having altered the formula as you suggested in your first line, it now works like a charm throughout the full work sheet.

Will now use your great website to find a way to get the average for each of the calculated sums, without having to manually add a divisor at each end of the formulas:

=SUM(IF(MOD(ROW($I$10:$I40),10)=0,$I$10:$I40,0))/4

=SUM(IF(MOD(ROW($I$10:$I50),10)=0,$I$10:$I50,0))/5

Again, many thanks

Jaydee

Answer 3

You should use

=SUM(IF(MOD(ROW($I$8:$I180),10)=8,$I$8:$I180,0))

Alternatively:

=SUM(IF(MOD(ROW($I$8:$I80)-ROW($I$8),10)=0,$I$8:$I80))

(No need to specify 0 as value_if_false; SUM will ignore FALSE values.)

Answer 4

Hija HansV,

once again many thanks.

You're a star.

Cheers,

Jaydee