Repeat a Formula every 10th Row

[jolly$]

Hello All. New member with fairly basic Excel knowledge. Can anyone help?
I want to repeat a formula in every 10th row in a column.
The formula is totaling 10 amounts in the column to it's left.
Overall excel sheet is very long and would take ages to copy/paste all the way down every 10th row.
Any guidance would be greatly appreciated.
Many thanks.

[AliGW]

Welcome to the forum.

Which version of Excel do you have? There is no 3.2 ...

There are instructions at the top of the page explaining how to attach your sample workbook (yellow banner: HOW TO ATTACH YOUR SAMPLE WORKBOOK). Screenshots are of little practical use as we cannot manipulate them.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

[jolly$]

Hi AliGW. Thanks for your message. I am using Excel 2016. Will now try to attach a sample workbook. Thanks.

[AliGW]

Please update your forum profile with that information.

[jolly$]

Hi, Hopefully sample excel sheet attached.

[jolly$]

Hi, As you will see from the Excel Sheet, it is not every 10th Row as I said Earlier, it is every 14th Row in column G. In that row, I want to total the 14 amounts to it's left in column F.
Thanks.

[TMS]

Try, row 14, copied down:

Please Login or Register  to view this content.

[jolly$]

Hi Trevor, Thanks for the suggestion. I must be doing something wrong as that formula in row 14 does not produce an amount, it remains showing as the formula and drag/drop down the column shows the formula in every cell. Appreciate you trying to help.

[TMS]

I'm guessing the cell/column is formatted as Text. Format as General and try again (re-enter the formula).

[jolly$]

The Column was formatted as "Currency". I changed it to "General" but it still just shows the Formula in the cell. Any other ideas?

[dan4ish]

Try this on Column G with after cpoing the formula.
Make sure there is no space prior to "="

Highlight Column G then go to

'Data' tab --> 'Text to Columns'--> Delimineted -->Next-->Uncheck all the Delimeniters-->Generals-->Finish

[jolly$]

Hi Trevor. My mistake (I had a "space" before your formula). Now working a treat. You have great knowledge. I really appreciate your help. Dougie.

[jolly$]

Thanks to you too Dan4ish. You were correct, I had a space before "="

[TMS]

You're welcome. Thanks for the rep.


If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

[jolly$]

Hi All, Used =IF(MOD(ROW(),14)=0,SUM(F1:F14),"") to provide an amount every 14 rows in a column which was helpfully provided by "Guru" last September. I am trying to use it again in another much the same spreadsheet but get a message "You've entered too many arguments for this function". Could someone advise if they can see anything wrong with the Function.
Appreciate your help.

[TMS]

Post 7: =IF(MOD(ROW(),14)=0,SUM(F1:F14),"")
Post 15: =IF(MOD(ROW(),14)=0,SUM(F1:F14),"")

They look the same to me (as copied and pasted here).


Fast answers need visual help. Please read the yellow banner at the top of this page on how to attach a file and a mocked up solution.

[jolly$]

Hi, Thanks for checking. Any idea which I would get the message ""You've entered too many arguments for this function". ??

[Pete_UK]

Maybe you have missed a closing bracket somewhere, or put an extra comma in the formula by mistake. Copy the actual formula from your new workbook and paste it directly into a new post here, or provide a sample workbook so we could see for ourselves. (see yellow banner at top of screen)

Hope this helps.

Pete

[jolly$]

Hi Pete, Thanks for looking at this. The Formula I am using in the new workbook is =IF(MOD(ROW(),15)=0,SUM(F10:F24),"") It is being used in column G to make a sum of the amounts in F10:F24 and then repeat the formula every 15 Rows down Column G. Hope that makes sense. Dougie.

[TMS]

This would do it:

=IF(MOD(ROW(,14)=0,SUM(F1:F14),"")

Or this:

=IF(MOD(ROW(),14=0,SUM(F1:F14),"")

[TMS]

=IF(MOD(ROW(),15)=0,SUM(F10:F24),"")

Again, nothing wrong with this formula.

Are you copying it and pasting it here or re-typing it?

[jolly$]

Hi, I'm retyping it. I'll try copy/pasting it. Can't see why I get that error message!!! Doug.

[TMS]

You are re-typing it correctly. Try copying and pasting from your post into your worksheet.

[jolly$]

Hi, Tried copy/pasting. Still gives me the same message "You've entered too many arguments for this function". Seems strange... any other ideas?? Doug

[AliGW]

Attach your workbook, please - make sure it's desensitised. The formula is correct.

EDIT: Forget that - see below.

[AliGW]

I've put this into M14 and dragged it down:

=IF(MOD(ROW(),14)=0,SUM(F1:F12),"")

You will see it working in the attachment.

[TMS]

Seems strange... any other ideas?? Doug

Yes. As we keep asking/suggesting: post the workbook.

[TMS]

Another sample file with both formulae. No point us keep testing working formulae. We need to see the thing that's broken.

[AliGW]

@jolly$

In your sample workbook, just remove the = from the start of the formula that is not being accepted - then we can look at it and see what the problem is.

[jolly$]

Hi, I know I'm frustrating you but I cannot get this formula to work. Attached is part of the spreadsheet (with the formula in G15). Now producing a result there but not repeating down the page. I must be doing something wrong.
Doug.

[TMS]

Works for me:

Please Login or Register  to view this content.

[jolly$]

Thank you all for looking at this. I have no idea what I'm doing wrong. but it will not work for me. Appreciate you all trying to help me though. I'll just have to keep trying. Thanks. Doug

[AliGW]

Have you copied the formula down by dragging?

[jolly$]

Hi Ali, Not sure how to drag the formula down to the cell. Doug.

[TMS]

I am a little confused. If there was an error in the formula, you wouldn't be able to save the workbook. Hence Ali's suggestion that you remove the equals sign from the formula. But the workbook has a formula in it and it works. So, confused .

The formula works and drags down (for me).

[TMS]

See attached example.

[jolly$]

Me too TMS. I just don't know what is going wrong or I am doing wrong.

[AliGW]

There’s a little black square bottom right of the cell - grab it and drag downwards.

[TMS]

Or, just select the cell and copy it. Then select all the cells below it and paste the formula.

[jolly$]

Thanks for the help. The drag worked. Problem seems to have been headings I had on the Worksheet which seemed to interfere with the formula. All sorted now and working.
Your help is greatly appreciated. Many thanks.

[TMS]

You're welcome. Thanks for the rep.


Not sure I understand the cause or solution but, not to worry.

[AliGW]

I fail to see how the headings could have been at fault - but once again, without an engine to see, I can't tell you what's wrong under the bonnet. I think it would have been beneficial to you in the long run to get it properly diagnosed, but there we are.

Thanks for the rep.