Issue with Array Formula

[Mike Savage]

Hi
Hoping someone might be able to help me with this one.

I have a formula to add every forth row in a range which is working fine (only a small range at the moment while testing). This is

Formula is =SUM(H11:H18*(MOD((ROW(H11:H18)-7),4)=0)) where my range is H11:H18 and this SUMS H11 and H15. This is working well but I am needing to make the range dynamic to deal with the data area expanding which is where I am running into problems.

H11 will stay the same but H18 will expand as rows are added in sets of four.
My plan was to do similar as I have done before and Name a Cell and then use the Row Function to get the row number.
I.e. I have named Cell A19 as "WorldWideEnd" and then Row(WorldWideEnd)-1 gives me 18.

If I then amend my formula to be

=SUM(INDIRECT("H11:H"&(ROW(WorldWideEnd)-1))*(MOD((ROW(INDIRECT("H11:H"&(ROW(WorldWideEnd)-1)))-7),4)=0))

It returns "#Value!"

I tried putting the Row(WorldWideEnd)-1 is a different cell and referencing this as below where H39 is where Row(WorldWideEnd)-1 is calculated

=SUM(INDIRECT("H11:H"&H39)*(MOD((ROW(INDIRECT("H11:H"&H39))-7),4)=0))

and this works but I cannot seem to get it to work as one formula.

Hoping someone can spot what mistake I am making as having spent a little while looking at it is is driving mad !

Thanks in advance
Mike

[Jonmo1]

I always like to try finding alternate solutions to the 'every x rows' problems.
There is almost always an easier and more efficient way.

Is there something in another column that can be used as a Key to flag which rows need to be included in the sum?
Then we can use a sumif, like Sum Column H IF the corresponding cell in A = "some value"

Can you attach a sample workbook?
Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[MarvinP]

Hi Mike,

See if this works for you:

=SUM(HColumn*(MOD((ROW(HColumn)),4)=0))
entered with CSE confirm,

But look at the Dynamic Named Range in the Names Manager to see the other important part of the problem.

Array Sum of Mod 4 Row numbers using DNR.xlsx

http://www.onlinepclearning.com/exce...anges-6-types/

[Zer0Cool]

A couple things here.

As far as the expanding range, you have many ways to handle this in a more elegant way.

• Create a dynamic named range for H11:Hx
• Put the contents of your data into a table structure and use structured references to the column
• Other less advisable methods...

Also, change your SUM array to a SUMPRODUCT, its generally at least 5-10% faster to calculate (important as your data grows) and I have seen it improve SUM array speeds to the tune of cutting calculation times down 1/3 the time SUM array took. More background on that can be found here (under "Consider options for using SUMPRODUCT for multiple-condition array formulas")

[Mike Savage]

Thanks
Have uploaded a sample with the Yellow Areas showing the various attempts.

Good thought on using a different column to define which rows are needed. I could do this with Column F in the sample (think I was trying to be too clever !!)

Would be interesting if there is a solution though now have got this far.

Thanks
Mike

[Mike Savage]

Thanks have popped out but will give this a proper look when I am back at my desk

[Jonmo1]

Yep, column F is already setup as an ideal 'flag'.

In H25 and filled right/down as needed
=SUMIF($F$11:$F$18,F26,H$11:H$18)

[Mike Savage]

Thanks for all the help. Sorted using the column F Flag.
Mike

[Jonmo1]

You're welcome.