Sum every 6th cell in a row.

[Elgee]

Hello,
I have read many posts re: summing every nth cell. I apologize - I am still
confused as I am still somewhat of an excel novice.

I have a workbook where I track financial details for multiple projects over
the course of a year. Every 5 rows contains all the details for 1 project.

Column C Represents January, Column D Represents February etc.

In C11 I want to SUM the Forecast for all projects in January,
In D11 I want to SUM the Forecasts for all projects in February.

My project data starts at C17:
Cell C17 is the name of the Project.
Cell C18 is the Forecasted $ amount
Cell C19 is the Actual Dollar Amount
Cell C20 is the Variance (Forecast -Actuals)
Cell C21 is the EAC

All of that starts over again at C22 for my next project.

Please help - What is the formula I want to put in C11?

I have copy / pasted / modified a variey of formulas I have seen posted - I
am not getting the correct value.

Thank you so much!

 

[Don Guillett]

Look in the help index for MOD to get you started

 

[Elgee]

Hello,
I have read many posts re: summing every nth cell. I apologize - I am still
confused as I am still somewhat of an excel novice.

I have a workbook where I track financial details for multiple projects over
the course of a year. Every 5 rows contains all the details for 1 project.

Column C Represents January, Column D Represents February etc.

In C11 I want to SUM the Forecast for all projects in January,
In D11 I want to SUM the Forecasts for all projects in February.

My project data starts at C17:
Cell C17 is the name of the Project.
Cell C18 is the Forecasted $ amount
Cell C19 is the Actual Dollar Amount
Cell C20 is the Variance (Forecast -Actuals)
Cell C21 is the EAC

All of that starts over again at C22 for my next project.

Please help - What is the formula I want to put in C11?

I have copy / pasted / modified a variey of formulas I have seen posted - I
am not getting the correct value.

Thank you so much!

 

[Max]

Assuming 3 projects' worth of data within row 17 to 31 in col C across
In C11:
=SUMPRODUCT(--(MOD(ROW(C17:C31),5)=3),C17:C31)
will return the sum of what's in: C18,C23,C28 (ie the forecast amts)
Copy C11 across to return correspondingly for figs in cols D,E,F etc
Adapt the ranges to suit the actual extent of your data

To easily figure out what should the number be to equate the MOD within each
set of 5 rows [ie the "3" in MOD(...)=3], place this in any cell in row17
(the start row of data), say in B17, then copy down: =MOD(ROW(),5). You'd see
the repeating pattern of 2,3,4,0,1, 2,3,4,0,1 ... . So, for the forecast amts
in C18,C23.. use: 3, and for actual dollar amts in C19,C24.. use: 4, ie set
it as MOD(...)=4 in the expression.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,800 Files:359 Subscribers:56
xdemechanik