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:
=MAX(INDIRECT(ADDRESS(IF(ROW()-1="",ROW()-1,IF(ROW()-2="",ROW()-2,IF(ROW()-3="",ROW()-3,IF(ROW()-4="",ROW()-4,IF(ROW()-5="",ROW()-5,IF(ROW()-6="",ROW()-6,IF(ROW()-7="",ROW()-7,IF(ROW()-8="",ROW()-8,IF(ROW()-9="",ROW()-9,IF(ROW()-10="",ROW()-10,IF(ROW()-11="",ROW()-11,IF(ROW()-12="",ROW()-12,IF(ROW()-13="",ROW()-13,IF(ROW()-14="",ROW()-14,IF(ROW()-15="",ROW()-15,IF(ROW()-16="",ROW()-16,IF(ROW()-17="",ROW()-17,IF(ROW()-18="",ROW()-18,IF(ROW()-19="",ROW()-19,IF(ROW()-20="",ROW()-20,IF(ROW()-21="",ROW()-21,IF(ROW()-22="",ROW()-22,IF(ROW()-23="",ROW()-23,IF(ROW()-24="",ROW()-24,IF(ROW()-25="",ROW()-25,IF(ROW()-26="",ROW()-26,IF(ROW()-27="",ROW()-27,IF(ROW()-28="",ROW()-28,IF(ROW()-29="",ROW()-29,IF(ROW()-30="",ROW()-30,IF(ROW()-31="",ROW()-31,IF(ROW()-32="",ROW()-32,IF(ROW()-33="",ROW()-33,IF(ROW()-34="",ROW()-34,IF(ROW()-35="",ROW()-35,IF(ROW()-36="",ROW()-36,IF(ROW()-37="",ROW()-37,IF(ROW()-38="",ROW()-38,IF(ROW()-39="",ROW()-39,IF(ROW()-40="",ROW()-40,IF(ROW()-41="",ROW()-41,IF(ROW()-42="",ROW()-42,IF(ROW()-43="",ROW()-43,IF(ROW()-44="",ROW()-44,IF(ROW()-45="",ROW()-45,IF(ROW()-46="",ROW()-46,IF(ROW()-47="",ROW()-47,IF(ROW()-48="",ROW()-48,IF(ROW()-49="",ROW()-49,IF(ROW()-50="",ROW()-50,IF(ROW()-51="",ROW()-51,IF(ROW()-52="",ROW()-52,IF(ROW()-53="",ROW()-53,IF(ROW()-54="",ROW()-54,IF(ROW()-55="",ROW()-55,IF(ROW()-56="",ROW()-56,IF(ROW()-57="",ROW()-57,IF(ROW()-58="",ROW()-58,IF(ROW()-59="",ROW()-59,IF(ROW()-60="",ROW()-60,IF(ROW()-61="",ROW()-61,IF(ROW()-62="",ROW()-62,IF(ROW()-63="",ROW()-63,IF(ROW()-64="",ROW()-64,IF(ROW()-65="",ROW()-65,IF(ROW()-66="",ROW()-66,IF(ROW()-67="",ROW()-67,IF(ROW()-68="",ROW()-68,IF(ROW()-69="",ROW()-69,IF(ROW()-70="",ROW()-70,IF(ROW()-71="",ROW()-71,IF(ROW()-72="",ROW()-72,IF(ROW()-73="",ROW()-73,IF(ROW()-74="",ROW()-74,IF(ROW()-75="",ROW()-75,IF(ROW()-76="",ROW()-76,IF(ROW()-77="",ROW()-77,IF(ROW()-78="",ROW()-78,IF(ROW()-79="",ROW()-79,IF(ROW()-80="",ROW()-80,IF(ROW()-81="",ROW()-81,IF(ROW()-82="",ROW()-82,IF(ROW()-83="",ROW()-83,IF(ROW()-84="",ROW()-84,IF(ROW()-85="",ROW()-85,IF(ROW()-86="",ROW()-86,IF(ROW()-87="",ROW()-87,IF(ROW()-88="",ROW()-88,IF(ROW()-89="",ROW()-89,IF(ROW()-90="",ROW()-90,IF(ROW()-91="",ROW()-91,IF(ROW()-92="",ROW()-92,IF(ROW()-93="",ROW()-93,IF(ROW()-94="",ROW()-94,IF(ROW()-95="",ROW()-95,IF(ROW()-96="",ROW()-96,IF(ROW()-97="",ROW()-97,IF(ROW()-98="",ROW()-98,IF(ROW()-99="",ROW()-99,IF(ROW()-100="",ROW()-100,ROW()-100)))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))),COLUMN())):INDIRECT(ADDRESS(ROW()-1,COLUMN())))

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.

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

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!

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

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

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!

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