Series question in a formula

JoshuaMars

New Member
Hi there XL Family,

Hoping someone out there with more XL know how will know the answer to this.

In A1 of I have a basic sum formula.
=ATP!D11+ATP!D21+ATP!D31+ATP!D41+ATP!D51+ATP!D61+ATP!D71+ATP!D81+ATP!D91+ATP!D101+ATP!D111+ATP!D121+ATP!D131+ATP!D141+ATP!D151+ATP!D161+ATP!D171+ATP!D181+ATP!D191+ATP!D201+ATP!D211+ATP!D221+ATP!D231+ATP!D241+ATP!D251+ATP!D261+ATP!D271+ATP!D281+ATP!D291+ATP!D301+ATP!D311

In A2
=ATP!D321+ATP!D331+ATP!D341+ATP!D351+ATP!D361+ATP!D371+ATP!D381+ATP!D391+ATP!D401+ATP!D411+ATP!D421+ATP!D431+ATP!D441+ATP!D451+ATP!D461+ATP!D471+ATP!D481+ATP!D491+ATP!D501+ATP!D511+ATP!D521+ATP!D531+ATP!D541+ATP!D551+ATP!D561+ATP!D571+ATP!D581+ATP!D591

In A3
=ATP!D601+ATP!D611+ATP!D621+ATP!D631+ATP!D641+ATP!D651+ATP!D661+ATP!D671+ATP!D681+ATP!D691+ATP!D701+ATP!D711+ATP!D721+ATP!D731+ATP!D741+ATP!D751+ATP!D761+ATP!D771+ATP!D781+ATP!D791+ATP!D801+ATP!D811+ATP!D821+ATP!D831+ATP!D841+ATP!D851+ATP!D861+ATP!D871+ATP!D881+ATP!D891+ATP!D901

Etc Etc Etc

As you can see it does follow a series by adding 10 to each part of the sum.

Just wondering if there is a way to do a Edit-Fill-Series for this instead of having to type it out.

I have quite a lot to do for this large worksheet.

Hope someone can help.

Im sure it is easy if you know how.

Thanks heaps

Joshua
"An Aussie Novice"

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

just_jon

Legend
What should be the stop/start rows for the 1st formula?

JoshuaMars

New Member
As they are above.

I cut and put the cell formulas im using at the moment.

pgc01

MrExcel MVP
Hi Joshua

THis is an array formula equivalent to the one you have in A1

=SUM(N(OFFSET(ATP!\$D\$11,10*(-1+ROW(\$1:\$31)),0)))
This is an array formula and so MUST be entered with CTRL+SHIFT+ENTER and not just ENTER.

Is this what you want?
PGC

barry houdini

MrExcel MVP
You have less values summed in A2 than in A1 and A3 - is that correct?

Another way to replace A1 would be with

=SUMPRODUCT(--(MOD(ROW(ATP!D11:D311)-ROW(ATP!D11),10)=0),ATP!D11:D311)

JoshuaMars

New Member
the reason for that is A1 is January,
A2 is Feburary
A3 is march

What Im trying to do is is tally up the total for the month for each

This is the worksheet the total goes into.

The folling is the ATP worksheet where im trying get the total sum for.
The cells for the formula are only in the yellow.

Hope you understand. This is way I could just say between D2 and D311.

Hope having the pics helps out with this one.

Thankyou again.

Replies
2
Views
182

1,182,188
Messages
5,934,158
Members
436,933
Latest member
jamchemu1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back