Function to sum nth row in a dynamic range

[guerra1]

Hello,

I need some assistance writing an excel function to allow me to add the contents of every sixth row in a column. The column will have rows added and deleted therefore, I would like for the function to automatically update itself based on the current rows in the columns.

Here is an example of the column.

Column C
Row 1 - 5 have text as part of the form title
Row 6 is blank
Row 7 is the column title
Row 8 has numbers that I do not want to include in my sum
Row 9-10 have text
Row 11 has an amount and is the first set of data I would like to sum
Row 12 has text
From Row 13-18 the above mentioned rows in blue repeat and Row 17 has an amount and is the second set of data I would like to sum
From Row 19-24 the above mentioned rows in blue repeat and Row 23 has an amount and is the third set of data I wold like to sum
and so on five more times.
Row 55 is were for now I would like to put the function to sum all these cells.
I say for now because a user will have the ability to add or remove five more or less Rows in order to enter more information. They will enter these rows above the last Row were the function will be placed.

Row
7	Title
8	4
9	Text
10	Text
11	$50
12	Text
13	Title
14	7
15	Text
16	Text
17	$33
18	Text
...	Reat
55	Function

<TBODY>
</TBODY>

I know this is not the best way to build a form but this is what someone several pay levels above me has requested and I am trying to accomodate their request with little success.

I have tried using SUM, SUMPRODUCT, MOD, ROW, OFFSET functions but have not been successful in puting it together to make this work.

Any assistance would be very much appreciated.

If you need clarification on any of the information I described bellow or an example of the form please let me know.

Thanks in advance!

 

[Matty]

Welcome to MrExcel!

Could you not just use the SUM function and point it to the cells of interest?

If not, are there any row identifiers that we could use that would allow Excel to know which rows need to be considered in the sum?

Matty

 

[NBVC]

Try:

=SUMPRODUCT((MOD(ROW($C$1:INDEX($C$1:$C$55,ROW()-1))-ROW($C$1)+8,6)=0)+0,$C$1:INDEX($C$1:$C$55,ROW()-1))

 

[guerra1]

Welcome to MrExcel!

Could you not just use the SUM function and point it to the cells of interest?

If not, are there any row identifiers that we could use that would allow Excel to know which rows need to be considered in the sum?

Matty

Hi Matty,

Thank you for your reply.

Your suggestion would work if I was the person using this form, and thus updated the cells to be summed. Unfortunately this form will be used by employees of my organization and therefore the form needs the function to update itself as people add or remove rows.

Thank you none the less for taking the time to read my question and respond.

Regards

 

[guerra1]

Try:

=SUMPRODUCT((MOD(ROW($C$1:INDEX($C$1:$C$55,ROW()-1))-ROW($C$1)+8,6)=0)+0,$C$1:INDEX($C$1:$C$55,ROW()-1))

NBVC,

Thank you so much for responding to my question and for putting together such a beautiful function to help me with my problem.

I have just tested it and it works like a charm.

Very grateful for your assistance.

Have a great day!

Regards!

 

[davesweep]

Alternatively, you could use DSUM......

In A2 enter the formula =MOD((1+ROW(C7)),6)=0
In A3 enter the formula =DSUM(C6:C500,1,A1:A2)