# MAXIF Finding Max Value for Group in Column

#### johncharles1923

##### New Member
Hi - Thanks for helping. Trying to come up with a formula that will look at Column E groupings and give me the largest number in column G (for said grouping) in column H with an "X". I think it's a MAXIFS but can't quite finish the formula.

#### bosco_yip

##### Excel Ninja
In H3, formula copied down :

=IF(MAXIFS(G:G,E:E,E3)=G3,"X","")

#### johncharles1923

##### New Member
Thanks for your help! And noted on the sample file. I tried the formula and it works! Just need the data to populate on the row with the largest value. Looks like it defaults to the first brand/group value from Column E.

#### Attachments

• 10.2 KB Views: 7

#### johncharles1923

##### New Member
That's it! Thanks for the help again!

#### Peter Bartholomew

##### Well-Known Member
The formulas didn't just default to output the result against the first row of each block; they were carefully crafted to achieve that!
To bring the value forward on the same row
= IF( MAXIFS(Cases, Brand, Brand)=Cases, Cases, "")

#### vletm

##### Excel Ninja
johncharles1923
Why to use own column for mark those?
After You've find 'x' ... You need to check that value from Cases-column.
You could mark those as below and see those values at once.

#### Attachments

• 9.9 KB Views: 4

Excel 365

#### vletm

##### Excel Ninja
Why to duplicate data, if same could get eg with slicers or filter - below both outputs

#### Attachments

• 14 KB Views: 1

#### Peter Bartholomew

##### Well-Known Member
Just a different way of working. I never use filters (or stripy tables come to that).
Their use tends to mess up the appearance and balance of the worksheet and I prefer having both the original and filtered tables visible.

#### AlanSidman

##### Well-Known Member
An alternative solution is with Get and Transform

Code:
``````let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}, {"Brand", type text}, {"UOM", type text}, {"Cases", type number}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Brand"}, {{"Max", each List.Max([Cases]), type nullable number}})
in
#"Grouped Rows"``````

#### Attachments

• 25.6 KB Views: 2