Sum up list of selected columns

%3CLINGO-SUB%20id%3D%22lingo-sub-2253513%22%20slang%3D%22en-US%22%3ESum%20up%20list%20of%20selected%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2253513%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20all%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20any%20way%20that%20allows%20to%20sum%20up%20all%20selected%20columns%20based%20on%20row%3F%3C%2FP%3E%3CP%3EMy%20main%20source%20is%20a%20report%20that%20contains%20100%2B%2B%20columns%20and%201000%2B%2B%26nbsp%3B%20rows%2C%20but%20I%20only%20want%20to%20sum%20up%20the%20selected%2050%20columns.%26nbsp%3B%3C%2FP%3E%3CP%3EFormula%20with%20Index%20Match%20will%20be%20too%20long%20and%20I%20had%20tried%20to%20google%20up%20but%20cannot%20find%20a%20suitable%20solution.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20any%20expert%20here%20willing%20to%20share%20your%20ideas%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAdd-on%3A%20As%20data%20is%20confidential%20so%20unable%20to%20share%20out%20sample%20data%20%3CLI-EMOJI%20id%3D%22lia_disappointed-face%22%20title%3D%22%3Adisappointed_face%3A%22%3E%3C%2FLI-EMOJI%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2253513%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-2253568%22%20slang%3D%22en-US%22%3ERe%3A%20Sum%20up%20list%20of%20selected%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2253568%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1016880%22%20target%3D%22_blank%22%3E%40SookChing%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EExcel%20provides%20the%20SUM()%20function.%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Foffice%2Fsum-function-043e1c7d-7726-4e80-8f32-07b23e057f89%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Foffice%2Fsum-function-043e1c7d-7726-4e80-8f32-07b23e057f89%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2253665%22%20slang%3D%22en-US%22%3ERe%3A%20Sum%20up%20list%20of%20selected%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2253665%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%26nbsp%3Bfor%20your%20response%20but%20the%20simple%20SUM%20formula%20unable%20to%20solve%20my%20problem%20%3CLI-EMOJI%20id%3D%22lia_disappointed-face%22%20title%3D%22%3Adisappointed_face%3A%22%3E%3C%2FLI-EMOJI%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi all,

 

Is there any way that allows to sum up all selected columns based on row?

My main source is a report that contains 100++ columns and 1000++  rows, but I only want to sum up the selected 50 columns. 

Formula with Index Match will be too long and I had tried to google up but cannot find a suitable solution.

 

Is any expert here willing to share your ideas?

 

Add-on: As data is confidential so unable to share out sample data  

8 Replies

@SookChing 

Some ideas?

image.png

Thanks @Detlef Lewin for your response but the simple SUM formula unable to solve my problem  

Thanks Peter! However i don't think sumifs is appropriate as i got 50 columns
Could you please share an example with basic data of what you are trying to achieve? Not sure if I understand what the problem that you are trying to solve is.
The catch with SUMIFS is that the array 'mask' must be of the same dimensions as the original table. That essentially doubles the memory committed to your data. On the other hand the '...IFS' functions execute very fast. The FILTER function is best but requires Excel 365, but then, traditional Excel couldn't hit the trash can fast enough for me; I dislike traditional spreadsheet practices intensely! Probably the nested SUM/IF is the most widely deployable technique.

A further note: my row array 'selectedColumns' comprised 0/1 flags to indicate whether the column is required or not.

@TheRuster Hi Ruster, may refer the table below  

SookChing_1-1624685237020.png

I got 10 columns, and I only want to sum up 4 columns for each staff.
The basic sum or index match will do but as in my original post, I got more than 50 columns that need to be sum up at last. 

Not sure if there is any faster way for this.

 

@SookChing , you don't need 50 MATCHs, it could be as

=SUM(INDEX(range,ROW()-ROW($B$2),{1,4,7,8}))

and drag it down. Or, if you have list of columns to sum

=SUM(INDEX(range,ROW()-ROW($B$2),XMATCH(columnNames, headers))