Excel 2007 : Need help to create a summary table

Hi,
I have been stuck on this for a whole week. I am looking to create a simple summary table without macros.

We have a standard input box for each division with headers 2007, 2008 and 2009. Under each year there are two rows of numbers:- first row is for number of employees and second row is for the number of machines.

What I want to be able to do is create a summary sheet that totals the number of employees and also the number of machines by year.

The problems is that the data comes in through the year and there might be over 80+ divisions, making it over 80 input boxes. I want to be able to just add new boxes and have them automatically sum without fixing the formula in the summary sheet.

What i have tried is using the define name function but that doesnt work if I add new input boxes. I have also tried using a macro and getting that to add the same coloured cells. That didnt work.

Any ideas?

An "Input Box"...is that some sort of form control box, or just your term for a new cell with data in it?

I would design my sheet with unique data in unique columns, if possible. Then simple formulas like =SUM(C:C) would sum column C as I added new rows of data without me having to change anything in that summary cell.

If this isn't helpful, mockup a sample workbook showing the dilemma and your desired results, point out where the results came from if not obvious in your sample data.

Then click GO ADVANCED and use the paperclip icon to post up your workbook. We'll have a specific answer is we can see your specific scenario.

Put this formula in B3 and then copy across and down:

=SUMIF($A$8:$A$1000, $A3, B$8:B$1000)

If your Summary table at the top weren't sharing columns with the data we are trying to analyze, SUMIF allows you to use the entire column as references.