Apr 04 2021 06:58 PM - edited Apr 04 2021 06:59 PM
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 :(
Apr 04 2021 08:56 PM
Excel provides the SUM() function.
https://support.microsoft.com/en-us/office/sum-function-043e1c7d-7726-4e80-8f32-07b23e057f89
Apr 04 2021 11:24 PM
Thanks @Detlef Lewin for your response but the simple SUM formula unable to solve my problem :(
Apr 05 2021 12:40 AM
Apr 05 2021 01:30 AM
Apr 05 2021 01:48 AM
Jun 25 2021 10:31 PM
@TheRuster Hi Ruster, may refer the table below :)
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. :(
Jun 26 2021 03:15 AM
@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))