# Formula to Check Max Value in Multiple Sections with Data

1. ## Formula to Check Max Value in Multiple Sections with Data

I have a very long list (14000+ rows) that is separated by category and each category has different values associated with it.

For example:

Category 1 65465
Category 1 23563
Category 1 32165

etc.
The category list is extensive, and the amount of values per category also varies (some are 47+).

What i'm looking for is to have a formula auto-adjust to the amount of values for that category and then give me the Max value from that list.

The current formula i have now is:

As you can see, it has 100 nested IF statements, which in turn throws the error stating that there are too many Nests.
I don't know how to do a loop in the formula which is why i resulted to this long formula with all of the nested IFs.
Basically all the formula is doing is checking to see if the row above it is empty or not and if it is, it will stop there and use that address as the starter point for the MAX formula.

Is there any way to have this shortened so i can paste it below any section and have it adjust to the amount of values above it. Each category is separated by a blank row.

2. ## Re: Formula to Check Max Value in Multiple Sections with Data

It would help if you attached a sample Excel workbook.

To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post. Don't try to use the Paperclip icon (Attachments button), as it doesn't work on this forum.

Hope this helps.

Pete

3. ## Re: Formula to Check Max Value in Multiple Sections with Data

Unfortunately i am unable to provide the exact Excel sheet i am working on, as it contains confidential information.

But as a brief example of what it consists of:

Category ----- Number

Category 1 654654
Category 1 631531
Category 1 998877
Category 1 111111
Category 1 847444
(Formula here) - This would grab the highest number out of these 5

Category 2 877779
Category 2 887222
Category 2 987872
(Formula here) - This would grab the highest number out of these 3

Category 3 333145
Category 3 435432
Category 3 464642
Category 3 787999
Category 3 864341
Category 3 775222
Category 3 388888
(Formula here) - This would grab the highest number out of these 7

I just want to copy and paste the formula and have it auto-adjust to the amount of values between each blank rows.

I really hope this helps!

4. ## Re: Formula to Check Max Value in Multiple Sections with Data

Take a look at the MAXIFS function on Excel Help - I think it is available for your version.

It looks like you have a blank row underneath each category. Suppose you were to put the formula in cell B7 (based on your example above), The formula would be:

=MAXIFS(B\$2:B6,A\$2:A6,A6)

Note that the range goes up to the row above the one in which you put the formula (shown in red). You might want to format that cell so that it stands out, e.g. Bold and Red. Then you can copy the formula and paste it into the next blank cell (i.e. B11 in your example), then into the next blank cell (B19), and so on.

Alternatively, you could put this in C2:

=IF(B2="",MAXIFS(B\$2:B2,A\$2:A2,A1),"")

and copy it down column C - the result for each section will only show on the blank rows for each category.

Hope this helps.

Pete

5. ## Re: Formula to Check Max Value in Multiple Sections with Data

Hi,

If you don't have Office 365, then use this Array formula, to be confirmed by CSE (Control, Shift, Enter)

You can put the formula in the Blank row between Categories or anywhere on your sheet.

Excel 2016 (Windows) 64 bit
A
B
1
Category 1
654654
2
Category 1
631531
3
Category 1
998877
4
Category 1
111111
5
Category 1
847444
6
7
Category 2
877779
8
Category 2
887222
9
Category 2
987872
10
11
Category 3
333145
12
Category 3
435432
13
Category 3
464642
14
Category 3
787999
15
Category 3
864341
16
Category 3
775222
17
Category 3
388888
18
19
Category 1
998877
20
Category 2
987872
21
Category 3
864341
 Sheet: Sheet31

Excel 2016 (Windows) 64 bit
B
19
=MAX(IF(A\$1:A\$17=A19,B\$1:B\$17))
 Sheet: Sheet31

6. ## Re: Formula to Check Max Value in Multiple Sections with Data

Yes! The MAXIFS function was able to get me what i needed. However, that function is apparently only available to Office 365 users, which i am, but i am unable to use this function in the Excel program. I seem to only be able to use it in the web version of Excel. But that is an issue on its own. Thank you for the help!

7. ## Re: Formula to Check Max Value in Multiple Sections with Data

Try this
Enter in B19 and copy down

regular formula
Formula:
`Please Login or Register  to view this content.`

 v A B 1 Category 1 654654 2 Category 1 631531 3 Category 1 998877 4 Category 1 111111 5 Category 1 847444 6 7 Category 2 877779 8 Category 2 887222 9 Category 2 987872 10 11 Category 3 333145 12 Category 3 435432 13 Category 3 464642 14 Category 3 787999 15 Category 3 864341 16 Category 3 775222 17 Category 3 388888 18 19 Category 1 998877 20 Category 2 987872 21 Category 3 864341

8. ## Re: Formula to Check Max Value in Multiple Sections with Data

Originally Posted by Garyyyyyyyyyy
Yes! The MAXIFS function was able to get me what i needed. However, that function is apparently only available to Office 365 users, which i am, but i am unable to use this function in the Excel program. I seem to only be able to use it in the web version of Excel. But that is an issue on its own.
That's why I provided a Non-Office 365 version in my Post #5

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1