I am a construction estimator that uses a spreadsheet to input labor,
material and equipment costs for each of the construction task associated
with the project. The list of tasks can vary from a couple of hundred to
10,000 rows. As to the column count, I have about (25) columns containing
such items as the task name, quatity of materials or equipment, engineering
units, labor hours, labor cost, material costs, equipment costs and then the
total of all costs for that row item. All additional columns to the right of
the (25) are used to contain a copy of the rows total cost value that can
then be assigned up to (40) cost breakouts. The limit of (40) is the limit of
columns available in Excel 2003. While (40) cost breakout's seem like a large
number, they are limiting in thiis application. More often than not, we could
have a client that wants us to breakout our total costs among (100)
subtotals. The way in which I am assigning costs to one of the forty cost
breakouts is through an if statement; Example: IF cell E12 = 1, then copy the
cost value and put it into the cell AA12, if not, the value is 0. One
solution I am trying to work with is to perform the same IF statement but
position the copy location to rows rather than columns. It is a bit difficult
to fully explain my design without sending the spreadsheet. If that is needed
to answer my questions, please let me know. Any help would be appreciated.

Thank You, JAD

Let's say that column 25 (Y) contains the total costs on each row. You
can use column Z to identify which cost centre you want this to be
allocated to (eg using 1, 2, 3 etc). Then on row 1 of columns AA to BZ
(for example) you can enter the cost centre codes you have used (1 to
52 in this example). Then you could enter this formula in AA2:

=SUMIF($Z$2:$Z$10000,AA1,$Y$2:$Y$10000)

and copy this formula across to BZ2 - it will give you the total of
costs allocated to each cost centre. Of course, in XL2003 you will be
limited to about 230 cost centre codes going across the columns this
way if you have already used 26 columns, so you might want to arrange
the codes vertically and adjust the formula appropriately.

Is this what you meant?

Hope this helps.

Pete

On Apr 27, 5:08 pm, JAD wrote:
I am a construction estimator that uses a spreadsheet to input labor,
material and equipment costs for each of the construction task associated
with the project. The list of tasks can vary from a couple of hundred to
10,000 rows. As to the column count, I have about (25) columns containing
such items as the task name, quatity of materials or equipment, engineering
units, labor hours, labor cost, material costs, equipment costs and then the
total of all costs for that row item. All additional columns to the right of
the (25) are used to contain a copy of the rows total cost value that can
then be assigned up to (40) cost breakouts. The limit of (40) is the limit of
columns available in Excel 2003. While (40) cost breakout's seem like a large
number, they are limiting in thiis application. More often than not, we could
have a client that wants us to breakout our total costs among (100)
subtotals. The way in which I am assigning costs to one of the forty cost
breakouts is through an if statement; Example: IF cell E12 = 1, then copy the
cost value and put it into the cell AA12, if not, the value is 0. One
solution I am trying to work with is to perform the same IF statement but
position the copy location to rows rather than columns. It is a bit difficult
to fully explain my design without sending the spreadsheet. If that is needed
to answer my questions, please let me know. Any help would be appreciated.

Thank You, JAD