multiple cells into 1

[rwb20141380]

I am trying to figure out how much dirt we are going to have to remove per structure. A structure consists of 2 holes with different amounts of soil being removed from each hole.

For example: I need structure 3 that has a right hole with 4.5 cy of soil to be removed and a left hole with 3 cy of soil to be removed. I want it so that a formula that I can easily transfer will combine the 2 lines into one quantity of soil that needs to be removed.

I have tried adding them, using summation, breaking up the numbers, and using the fill handle but it doesn't pick up on the pattern. Any ideas on how to do this efficiently? Thank you very much.

[#2 · Accepted solution]

@rwb20141380 

When you copy a formula down, a relative cell reference like =A1 will be incremented by one row only, to return =A2. Excel does not recognize a pattern and will not apply a pattern.

There are several other things you could do.

Split the structure number into two columns, so that one column has only the number and the other one has the text. 

- now you can build a pivot table that totals all values for the same structure number

- or use Sumif functions or the new Dynamic Array formulas currently only available in Office 365 Insider.

[#3]

@Ingeborg Hawighorst  You are a saint. Thank you