Thanks:  0
Likes:  0

# Thread: Lookup a column of values with the largest sum

1. ## Re: Lookup a column of values with the largest sum

Sorry, correction. #16 should read A2:K1098. Otherwise the same until #20. Stupid me, type error.
/

2. ## Re: Lookup a column of values with the largest sum

Originally Posted by Pille
No, A is just counting rows.
/Per
Is this row counting done by your query or some formula in A?

Query

4. ## Re: Lookup a column of values with the largest sum

Originally Posted by Pille
Query
Define Lrow in Formulas | Name Manager as referring to:

=MATCH(REPT("z",255),data!\$B:\$B)

Adjust the sheet name if your data lands in a different sheet.

Define Nums in Formulas | Name Manager as referring to:

=data!\$D\$2:INDEX(data!\$K:\$K,Lrow)

Define Header in the Name Manager as referring to:

=data!\$D\$1:\$K\$1
__________________________________

data (contains the data to process)

 Row\Col A B C D E F G H I J K 1 row count textfield-1 textfield-2 X1 X2 X3 X4 X5 X6 X7 X8 2 1 jad damon 1 16 4 9 3 2 vad nate 8 3 2 1 4 3 nad joe 11 5 9 6 5 4 wad john 6 20 6 5 qad jon 15

processing

 Row\Col A 1 3 2 5 3 X3 4 4 5 2 6 9 7 20 8 15 9 10 11 12

In A1 of processing control+shift+enter, not just enter:

=MATCH(MAX(MMULT(TRANSPOSE(Nums)+0,ROW(Nums)^0)),MMULT(TRANSPOSE(Nums)+0,ROW(Nums)^0),0)

In A2 of processing just enter:

=COUNT(INDEX(Nums,0,A1))

In A3 of processing just enter:

In A4 of processing just enter and copy way down:

=IF(ROWS(\$A\$4:A4)>\$A\$2,"",INDEX(Nums,ROWS(\$A\$4:A4),\$A\$1))

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•