Function for summing corresponding rows

%3CLINGO-SUB%20id%3D%22lingo-sub-1740833%22%20slang%3D%22en-US%22%3EFunction%20for%20summing%20corresponding%20rows%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1740833%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%20I'm%20trying%20to%20find%20a%20function%20that%20can%20sum%20up%20two%20numbers%20if%20their%20row%20number%20matches.%20For%20example%2C%20if%20Column%20A%20and%20Column%20B%20are%20my%20data%20sets%2C%20then%20I%20want%20a%20general%20function%20f(A_n%2C%20B_n)%20%3D%20A_n%20%2B%20B_n%20whose%20results%20will%20pop%20up%20in%20Column%20C.%20I'm%20aware%20that%20you%20can%20do%20a%20sum%20and%20then%20drag%20the%20AutoFill%20function%20over%20the%20sets%20I%20have.%20But%2C%20my%20Columns%20A%20and%20B%20are%20extremely%20long%20so%20dragging%20AutoFill%20presents%20a%20nuisance.%20Hoping%20someone%20can%20help%20me%20define%20a%20function%20with%20Excel's%20Formula%20feature%20or%20maybe%20using%20tables.%20Any%20assistance%20appreciated.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIncluded%20a%20picture%20of%20an%20example%20excel%20sheet%20to%20help%20visualize.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1740833%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1741489%22%20slang%3D%22en-US%22%3ERe%3A%20Function%20for%20summing%20corresponding%20rows%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1741489%22%20slang%3D%22en-US%22%3E%3CP%3E%3CEM%3EWrite%20this%20formula%20in%20cell%20C1%20and%20fill%20down%3A%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DIF(SUM(A1%2BB1)%3D0%2C%20%22%22%2C%20SUM(A1%2BB1))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CUL%3E%3CLI%3EYou%20may%20adjust%20cell%20references%20in%20the%20formula%20as%20needed.%3C%2FLI%3E%3C%2FUL%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1741490%22%20slang%3D%22en-US%22%3ERe%3A%20Function%20for%20summing%20corresponding%20rows%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1741490%22%20slang%3D%22en-US%22%3EEnsure%20your%20fill%20handle%20option%20is%20enabled%20(File%2FOptions%2FAdvanced%2FEnable%20fill%20handle%20and%20cell%20drag%20and%20drop).%3CBR%20%2F%3E%3CBR%20%2F%3EEnter%20%3DA1%2BB1%20in%20cell%20C1.%3CBR%20%2F%3E%3CBR%20%2F%3EThen%2C%20hover%20the%20cursor%20over%20the%20bottom%20right%20of%20cell%20C1%20(over%20the%20little%20black%20square).%20When%20the%20cursor%20turns%20to%20a%20black%20cross%2C%20double%20click.%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E
Highlighted
Visitor

Hello, I'm trying to find a function that can sum up two numbers if their row number matches. For example, if Column A and Column B are my data sets, then I want a general function f(A_n, B_n) = A_n + B_n whose results will pop up in Column C. I'm aware that you can do a sum and then drag the AutoFill function over the sets I have. But, my Columns A and B are extremely long so dragging AutoFill presents a nuisance. Hoping someone can help me define a function with Excel's Formula feature or maybe using tables. Any assistance appreciated. 

 

Included a picture of an example excel sheet to help visualize. 

4 Replies

Write this formula in cell C1 and fill down:

 

 

=IF(SUM(A1+B1)=0, "", SUM(A1+B1))

 

  • You may adjust cell references in the formula as needed.
Highlighted
Ensure your fill handle option is enabled (File/Options/Advanced/Enable fill handle and cell drag and drop).

Enter =A1+B1 in cell C1.

Then, hover the cursor over the bottom right of cell C1 (over the little black square). When the cursor turns to a black cross, double click.

Highlighted

@robbox 

In you case what @JMB17 suggested is most effective. Just in case, if formula is not in adjacent column, e.g. in column F, enter formula in F1, in Name Box (under top left of the sheet grid) type the reference on the range to be filled (e.g. F1:F999999), Enter (range will be selected), Ctrl+D (formula will be filled into the range).

If to fill horizontally, i.e. the row, same or similar but with Ctrl+R

Highlighted
Just to throw out one other option for the op in that scenario, I normally select a cell in the column containing the existing data (say column B). Then, hit Ctrl+Down Arrow to get to the bottom row of the range. Then go over to the column I want to fill and input something (anything) in the cell in the column I want to fill. Then ctrl+up arrow, input the formula, copy, ctrl+shift+down arrow, and enter (paste).