An array formula to sum row max

U

ucamms

I have a data range of 1000 rows and 20 columns. I need a sum of the
maximum number of each rows. I usually add another column to calculate
row max. But is it possible to eliminate this intermediate step and
just use a single cell array formula to return the sum of row maxes?

Thanks
 
D

Domenic

Assuming that A1:T1000 contains your data, try...

=SUMPRODUCT(SUBTOTAL(4,OFFSET(A1:T1000,ROW(A1:T1000)-MIN(ROW(A1:T1000)),0,1)))

Hope this helps!
 
U

ucamms

It wooked. Very clever. I didn't think of using offset() and row(
combination.

Thank you for helping
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Max Length in a row 1
Sum Formula with variable range 2
How to use vlookup in an array formula 2
Dynamic Sum Array Formula Input Help 3
sum to recognize new rows 3
Insert a Row and then Sum 1
Row() and SUM() ?s 7
SUMIF with multiple columns in sum range 5

Top