# Repeat a Formula every 10th Row

1. ## Repeat a Formula every 10th Row

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.

2. ## Re: Repeat a Formula every 10th Row

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.

3. ## Re: Repeat a Formula every 10th Row

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

5. ## Re: Repeat a Formula every 10th Row

Hi, Hopefully sample excel sheet attached.

6. ## Re: Repeat a Formula every 10th Row

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.

7. ## Re: Repeat a Formula every 10th Row

Try, row 14, copied down:
Formula:
`Please Login or Register  to view this content.`

8. ## Re: Repeat a Formula every 10th Row

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.

9. ## Re: Repeat a Formula every 10th Row

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

10. ## Re: Repeat a Formula every 10th Row

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

11. ## Re: Repeat a Formula every 10th Row

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

12. ## Re: Repeat a Formula every 10th Row

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.

13. ## Re: Repeat a Formula every 10th Row

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

14. ## Re: Repeat a Formula every 10th Row

You're welcome. Thanks for the rep.

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.

15. ## Re: Repeat a Formula every 10th Row

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.

16. ## Re: Repeat a Formula every 10th Row

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

17. ## Re: Repeat a Formula every 10th Row

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

18. ## Re: Repeat a Formula every 10th Row

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

19. ## Re: Repeat a Formula every 10th Row

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.

20. ## Re: Repeat a Formula every 10th Row

This would do it:

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

Or this:

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

21. ## Re: Repeat a Formula every 10th Row

=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?

22. ## Re: Repeat a Formula every 10th Row

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

23. ## Re: Repeat a Formula every 10th Row

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

24. ## Re: Repeat a Formula every 10th Row

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

25. ## Re: Repeat a Formula every 10th Row

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

EDIT: Forget that - see below.

26. ## Re: Repeat a Formula every 10th Row

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.

27. ## Re: Repeat a Formula every 10th Row

Seems strange... any other ideas?? Doug
Yes. As we keep asking/suggesting: post the workbook.

28. ## Re: Repeat a Formula every 10th Row

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

29. ## Re: Repeat a Formula every 10th Row

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

30. ## Re: Repeat a Formula

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.

31. ## Re: Repeat a Formula every 10th Row

Works for me:

``Please Login or Register  to view this content.``

32. ## Re: Repeat a Formula every 10th Row

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

33. ## Re: Repeat a Formula every 10th Row

Have you copied the formula down by dragging?

34. ## Re: Repeat a Formula every 10th Row

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

35. ## Re: Repeat a Formula every 10th Row

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

36. ## Re: Repeat a Formula every 10th Row

See attached example.

37. ## Re: Repeat a Formula every 10th Row

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

38. ## Re: Repeat a Formula every 10th Row

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

39. ## Re: Repeat a Formula every 10th Row

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

40. ## Re: Repeat a Formula every 10th Row

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.

41. ## Re: Repeat a Formula every 10th Row

You're welcome. Thanks for the rep.

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

42. ## Re: Repeat a Formula every 10th Row

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.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1