- 541K Members
- 6,302 Online
- 644K Conversations

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

- Home
- :
- Excel
- :
- General Discussion
- :
- Re: Find the row that has the maximum value across a number of columns

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

- Home
- :
- Excel
- :
- General Discussion
- :
- Re: Find the row that has the maximum value across a number of columns

Conversation Options

- Subscribe to RSS Feed
- Mark Conversation as New
- Mark Conversation as Read
- Pin this Conversation for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-05-2019 12:13 AM

I have a table that has columns which represent week numbers and rows which represent people. The cells have a number in for each week against each person. What formula can I use to find out the person that has the highest total value across a number of weeks? For example, I want to select weeks 1 to 5 to see who scored the most points

Labels:

12 Replies

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-05-2019 01:29 AM

@JNollett can you please share the file?

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-05-2019 01:43 AM

You may use an array formula (entered with Ctrl+Shift+Enter) similar to B16 of the attached file, which is:

**=LOOKUP(2,1/(MMULT(B$2:J$11,****TRANSPOSE(B$1:J$1^0*(B$1:J$1>=B$13)*(B$1:J$1<=B$14)))=B$15),****A$2:A$11)**

Note that Column K was presented therein for the sole purpose of verifying the correctness of the result obtained from the foregoing formula.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-08-2019 01:13 PM

Hi @Twifoo

I love the formula, but I have a minor problem in that my data is in a table, so the headers are actually headers and not just cells, so the first part of your formula, that equals 42, returns 0 (zero). I tried changing B$1:J$1 to Table2[[#Headers],[1]:[52]], but that doesn't work and returns 0 (zero) too. Any ideas?

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-08-2019 02:14 PM

In a table, the numbered weeks will actually be text. You therefore need either convert the 'To' and 'From' values to text or, conversely, convert the header row to values.

My programming style is not the same as @Twifoo 's so I defined a name '**mask**'

**= (VALUE(Table1[#Headers])>=From) * (VALUE(Table1[#Headers])<=To)**

which I use to filter out unwanted columns. My formula to sum across rows

**= MMULT(Table1, TRANSPOSE(mask) )**

gives the '**score**'.

From there, I part company with @Twifoo and, I expect, you because I am using the latest Office 365 version. I simply sorted the names descending by score

**= @ SORTBY( Name, score, -1 )**

and returned the first value only.

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-08-2019 05:10 PM

My suggested formula was a sample. Please attach your sample file for the specifics.

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-08-2019 10:18 PM

In the attached version for the file, I converted A1:J11 as Table1. Column L and B15 are presented for verification purposes only. Thus, the revised array formula in B16 is:

**=LOOKUP(2,1/(FREQUENCY(0,****1/(1+MMULT(Table1[[1]:[9]],****TRANSPOSE(Table1[[#Headers],[1]:[9]]^0*****(--Table1[[#Headers],[1]:[9]]>=B$13)*****(--Table1[[#Headers],[1]:[9]]<=B$14)))))),****Table1[Name])**

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-09-2019 12:31 AM

@TwifooThat worked until I added data from the next month. Take a look.

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-09-2019 12:32 AM

@Peter BartholomewI am using the latest version of Office 365 too, but SCORE is not a field in my data.

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-09-2019 05:44 AM

I defined the Name '**score**' using Name Manager. The formula goes into the Refers To dialogue box.

**= MMULT(Table1, TRANSPOSE(mask) )**

Whenever '**score**' appears in a formula on the worksheet, Excel will evaluate it as an array formula without requiring Ctrl+Shift+Enter [Office 365 is moving to evaluate all formulas as array formulas by default]. To display

**= score**

on a worksheet may require CSE (depending on the version of Excel) but

**= MAX( score )**

will work correctly irrespective of the version.

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-09-2019 03:17 PM

@Peter BartholomewHow does it work in the file that I attached though, as I cannot appear to get it to work. I don't understand the @sortby command either.

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-09-2019 04:22 PM

Seems I still had the workbook in memory on my laptop. It also contains an implementation of one of @Twifoo's formulas. One thing I did when creating the table was to exclude the Name and Totals columns, leaving a blank column to separate them. This allows me to use

**VALUE(Table1[#Headers])**

which grows dynamically with extra columns, rather than

**VALUE(Table1[[#Headers],[1]:[9]])**

which would need to be edited to include additional columns.

The SORTBY column is a new function, introduced last September, and gradually being rolled out as part of the Dynamic Array changes.

**= @ SORTBY( Name, score, -1 )**

sorts the 'Name' array by 'score' in descending order. The '@' sign before the an array result truncates the result to a single value (otherwise I would get a sorted version of the entire list of names).

At present, you will have the new function if you are signed up to Office 365 insider (weekly updates); it is in the process of being rolled out to 'monthly' and 'semi-annual' may have some time to wait.

I think the term

**(Table1[[#Headers],[1]:[9]])****^0**

is redundant since the following terms will give the same result without it.

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-09-2019 09:56 PM

I agree. The two conditional clauses cause such redundancy. If there were only one conditional clause, then no such redundancy would have occurred.

Thanks for your keen observation.

Related Conversations

Restrict Edit access on columns on user/group basis.

Vimmi Rawat
in
SharePoint
on
02-04-2020
67
Views

0 Likes

0 Replies

Outlook Message box GUI blacked out when sending attachment through Excel

midohioboarder
in
Office 365
on
01-24-2020
216
Views

0 Likes

0 Replies

PWA fullscreen like IE11 kiosk mode

rogihee
in
Discussions
on
10-19-2019
424
Views

0 Likes

5 Replies

Trying to add multiple users to distribution group and getting error: Cannot validate argument

Test SharePoint
in
Office 365
on
10-12-2019
834
Views

0 Likes

4 Replies

need macro to find and copy range of data between specified cells.

spike3rd
in
Office 365
on
10-09-2019
150
Views

0 Likes

0 Replies

Taking values from certain rows based on data input

Drakerla
in
Excel
on
09-17-2019
176
Views

0 Likes

5 Replies

Share

Popular

Learning Resources

Programs

Values

- Contact us
- Privacy & cookies
- Terms of use
- Trademarks
- About our ads
- © Microsoft