Repeating a formula

[ptk]

Hello guys, I hope you can help me on this.
I am not sure how to apply a formula in this situation:

I have 6900 values in column C, and 69 values in col E and F. The simple formula in col G (=C1+E1*F1) is to take each value in C but only use col E and F down to row 69. When it reaches row 70, it should read (=C70+E1*F1) and cycle through E and F from row 1 to 69 again as C continues all the way to the bottom.

I have included a sample file. All suggestions to how to solve this problem with least coding is appreciated.

[Ace_XL]

Try

=C1+INDEX(E:E,IF(MOD(ROW(),COUNT(E:E))=0,COUNT(E:E),MOD(ROW(),COUNT(E:E))))*INDEX(F:F,IF(MOD(ROW(),COUNT(F:F))=0,COUNT(F:F),MOD(ROW(),COUNT(F:F))))

EDITED

[Richard Buttrey]

Hi,

Couldn't see a sample file but does
=INDIRECT("C"&MOD(ROW(),69))+E1*F1
in G1 copied down work?

[ptk]

Thank you so much for your efforts. I really appreciate the time you put on this forum as your expertise makes my work so much better. However, the provided formulas produce errors in my file. I understand this is not a simple issue. Therefore, this time I have attached a sample file for your reference so you can get an idea of what I am after. Have a look at the formula column (G) and follow it down to row 71 and see how it changes for every 69th row. Thanks again for your time and expertise!

Best wishes,
p

[icestationzbra]

give this a try in cell G2 and then drag-fill down:

Please Login or Register  to view this content.

[ptk]

Your suggestion above worked! Great many thanks.

Now I want to apply the following sum formula:
=COUNTIF($O$2:$O$69;"5")+COUNTIF($O$2:$O$69;"2")+COUNTIF($O$2:$O$69;"1")+COUNTIF($O$2:$O$69;"X")

...which calculates how many times the characters/integers 5, 2, 1 and X occur on row 2 to 69.

However, I would need this formula to be repeated every 68th time, so from row 70 to 137 it should say
=COUNTIF($O$70:$O$137;"5")+COUNTIF($O$70:$O$137;"2")+COUNTIF($O$70:$O$137;"1")+COUNTIF($O$70:$O$137;"X")

Having more than 10,000 rows, this would be a time-consuming task manually done. Are there any ways to get around this with an array formula?

All help and comments are appreciated.

[icestationzbra]

since you have not provided a sample file for the latest problem, i have reused the file from post #4. look at column J for the solution to your new problem. adapt the formula to your actual datafile.

UPDATE:

this should solve your problem.

[ptk]

Thank you so much for your swift reply and your efforts to solve this problem. I appreciate it sooooo much!!!
Unfortunately, the formula returns "-" in all rows except the last row in each Id which all equal 68, which is wrong. However, I now also understand how important it is to enclose a sample file. So here is one enclosed!

The column 'TD all' calculates how many times 5, 2, 1 and X occur in column B for each Id in column A. As each Id is repeated 68 times, the formula is repeated 68 times in row 2-69, then the range is changed to row 70-137 in row 70-137, etc. I have more than 10,000 rows in the original file, I cannot change the rows in my formula in column C manually as I have done here. So I need a formula that calculates the characters 5, 2, 1 and X totally for each Id in column A.
((You can also say that I need a formula that calculates the characters 5, 2, 1 and X within every 68 cells starting from row 2.))
Later on, I will need to calculate according to the formulas in col E, F and G, but I guess that will be easy to change and paste.
All help is deeply appreciated and will be acknowledged.
The best
Ptk