Auto sum/grouping by row based on month

[witchcraftz]

I have data that auto populates from another sheet, it puts data into rows:

Actual
Planned
Forecast
Earned

I need to auto-sum my data for each row by month. So for example anything in the Actual row, where the date is between 2012/12/01 and 2012/12/31 should be summed together.

Pivot tables don't work since they only work on column arranged data and need to be rebuilt when the data changes.

Any ideas?

Sample sheet attached

[vlady]

try something like this

=SUMPRODUCT(--(MONTH(B1:Z1)=12),IF(ISNA(B2:Z2),0,B2:Z2)) -array formula (ctrl+shift+enter) not just enter

the bold =12 means month 12

[Pete_UK]

In the attached workbook I've put the start date and end date in B9 and C9. Then in B12 I have this formula:

Please Login or Register  to view this content.

which can then be copied down. Change B9 and/or C9 for a different day range.

Hope this helps.

Pete

[witchcraftz]

These have both provided me some more ideas however I am still not yet at a solution.

The biggest issues are:
1- the start and end dates may change
2- there will be multiple months that are the same, I can't simply drag it across to populate