multiple cells into 1

New Contributor

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 Replies



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.

@Ingeborg Hawighorst  You are a saint. Thank you

Related Conversations
Bitlocker keys not visible in Active Directory
Anez1987 in Windows 10 management on
1 Replies
Apply locked for editing
martinarenkacouk in Excel on
2 Replies
Separate red and black into two columns
Shikir23 in Excel on
3 Replies
Linking Adjacent Rows
ashwinash112 in Excel on
1 Replies