To Sum all columns to right

[vradhak7]

Hello VBA Users,
Suppose I want to Sum all values of (D2,E2,F2.....till the end of last used column) and If i want the result to be displayed in C2..What would the formula that i need to use to do it? Your insights please. Thanks in advance.

Regards
Vradhak7

[Aussiebear]

Is this by any chance posted on another forum?

[vradhak7]

no..not in any other forum

[lecxe]

Hi

You could sum till the last value:

=SUM(D2:INDEX(2:2,MATCH(1E+300,2:2)))

or you could also simply sum till the end of the row, simpler formula

=SUM(D2:INDEX(2:2,COLUMNS(2:2)))

or even simpler, like

=SUM(D2:Z2)

if you are sure that the data will never go beyond column Z.

[Bob Phillips]

Why not just use

=SUM(D2:XFD2)

[lecxe]

Why not just use

=SUM(D2:XFD2)

That is surely a simple and efficient solution.

I personally don't like to hardcode the last column. In this case it assumes that you are working an excel version for which XFD is the last column, like for ex. excel for windows 2007+
For other versions of excel in windows, mac, mobile, etc. the last column may be another one.

=SUM(D2:INDEX(2:2,COLUMNS(2:2)))

will work for any number of columns in any version of excel.


It's however not so simple of efficient, so it's a trade-off.

Maybe best to decide on a case-by-case basis, choosing the simplest formula that one is sure that will work.