Forum Discussion
Sum up list of selected columns
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
- 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.
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))
- Thanks Peter! However i don't think sumifs is appropriate as i got 50 columns 😞
- 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.
Excel provides the SUM() function.
https://support.microsoft.com/en-us/office/sum-function-043e1c7d-7726-4e80-8f32-07b23e057f89
Thanks Detlef_Lewin for your response but the simple SUM formula unable to solve my problem 😞
