Sum(max(col1),max(col2),...) Question

[axg275]

Hey Guys,

I have a question. I am trying to find the max of each column and sum it up such as this: Sum(max(col1),max(col2),...) .

Unfortunately, i have 200 columns and this operation will require a long time to construct and move around.

Is there a better way to write this in one swoop?

Thank you!

[hrlngrv]

You don't show your Office/Excel version. There are fairly simple ways to construct formulas as text strings in more recent versions. If you have TEXTJOIN, you could use something like

Please Login or Register  to view this content.

to produce this sort of formula for C3:C102 through DZ3:DZ102. Then copy the cell containing this formula, and paste-special as values on top of it.

If that's not close enough to one swoop or you have an older version of Excel which lacks TEXTJOIN, you may be better off using VBA user-defined functions which handle the iteration by column internally, e.g.,

Please Login or Register  to view this content.

[axg275]

Thank you very much i will try this. I have office 2013 so I hippie this will work. Thanks again!

[hrlngrv]

Excel 2013 lacks TEXTJOIN, so you'd need to use the VBA approach. You'd use the function in cell formulas like =SumMaxByCol(C3:DZ102).

[XLent]

you might try something along lines of:

=SUMPRODUCT(SUBTOTAL(4,OFFSET(C:C,0,COLUMN(C1:GT1)-COLUMN(C1))))

[hrlngrv]

Should add a caveat whenever recommending using volatile functions which operate on thousands of cells much less potentially hundreds of millions of cells.