# 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

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
What should be the stop/start rows for the 1st formula?

As they are above.

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

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

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)

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
372

1,219,911
Messages
6,150,927
Members
450,994
Latest member
MacOrch

### 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.

### Which adblocker are you using?

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