Excel 2007 Sum specific colums, in multiple spreadsheets, between date ranges

[Roddboyd]

Good Evening,

I am Rodney Boyd and I have been reading the Q & A and am both impressed with the knowledge of excel and the professional answers that are given. I look forward to reading and posting questions within this forum.

[Pete_UK]

So, are you asking a question now (with your thread title) or just introducing yourself?

Pete

[Roddboyd]

Hi Pete,

I am building a payroll program and have a summary sheet of all data from multiple employees.

This summary sheet has
- 12 months (Column B)
- Calculates total pay/deductions for all employees based on date ranges

Currently, I have am using on the summary sheet =(SUMIFS(CPP_1,Date_1,">="&A15,Date_1,"<="&B15)+Sumifs(CPP_2,Date_2,....)
1. Where "_1" is sheet1, "_2" is sheet2 and
2. "A5" is beginning of Month and
3. "B5" is end of the month.

Although this works I have to update my summary sheet every time I add a new sheet.

Is there a formula that will update the summary sheet when I add a new employee sheet. EI. Sumifs(First:Last!E:E),(First:Last!B:B),month = Jan)

[Pete_UK]

You can't do that with SUMIFS. I would suggest that you have the SUMIFS formula on each employee sheet in the same cell (e.g. X1), then on your summary sheet you can have:

=SUM(first:last!X1)

You must ensure that any new sheet is enclosed between the first and last sheets.

Hope this helps.

Pete

[Roddboyd]

Thanks very much!!

If I understand correctly and created individual summary sheets horizontally. Ie

Gross pay (A) Jan (B) Feb(C) Mar(D) Deductions(M) Jan(N) Feb(O) Mar(P)

Where A-P are columns

Then the summary sheet can be set up vertically

Gross pay Deductions
Jan (first:last!B1) Jan (first:last!C1)
Feb (first:last!N1) Feb (first:last!O1)

Very logical and I shouldn't have to update the summary sheet!

Rodney