Using MAX with SUMIFS

Hi All,

I am trying to get the max of several data points. I'll explain. I'm looking at three columns..

1. Building
2. Cost Center
3. Number Values for 'x'

On a different worksheet, I want to SUM the Number Values Column IF it meets 'y' Building AND 'z' Cost Center.

Here's the twist. There are a total of 30 columns of number values to sum. Using the criteria above, in one formula, I want to get the MAX of the 30 sums that meet the criteria.

Right now, I have a formula that is not working...

=MAX(SUMIFS(Number Value Range to sum 1, Building Column, Building, Cost Center Column, Cost Center),(Number Value Range to sum 2, Building Column, Building, Cost Center Column, Cost Center),(Number Value Range to sum 3, Building Column, Building, Cost Center Column, Cost Center), etc.....

Try:

=MAX(IF((Building Column=Building)*(Cost Center Column=Cost Cente),Number Value Range to sum all columns))

confirmed with CTRL+SHIFT+ENTER

Hello Michael,

Will the max consist of the sum of numbers from a single column in rows where the criteria are met (rather than the sum of maximum number in each row where the criteria is met)? If so try

=MAX(SUMIFS(OFFSET(Number Value Range to sum 1,0,ROW(INDIRECT("1:30"))-1),Building Column,Building,Cost Center Column,Cost Center))

assumes that your 30 columns are in a single 30 column range......

No. You just need to list the whole 30 column range in the last part.

e.g.

=MAX(IF(($A$2:$A$100=Building)*($B$2:$B$100=Cost Cente),$C$2:$AD$100))

just remember to hold the CTRL+SHIFT keys down and hit ENTER to confirm it.

Edit: Sorry, I think daddylonglegs' is better... mine assumes there is one number in each column that might match, not the sums....

Edit: Sorry, I think daddylonglegs' is better... mine assumes there is one number in each column that might match, not the sums....

You would have to replace the words Building and Cost Center with cell references containing the search words... or if you want to hard code the search terms enclose them in double quotes...

BUT... I think what daddylonglegs posted is what you actually need. It looks more like what you are needing... his formula also needs to be confirmed with CTRL+SHIFT+ENTER to work... (you also need to replace the words with actual column/cell references).

Try:

=MAX(SUMIFS(OFFSET('All Data'!JO2:JO4996,0,ROW(INDIRECT("1:30"))-1),'All Data'!A2:A4996,"HRNP",'All Data'!X2:X4996,'FT Flex Sliced Info'!D22))

Note: the vertical range sizes have to be the same

Confirm with CTRL+SHIFT+ENTER

You have different size vertical ranges..

As I had mentioned, then need to be the same.

You can't mix JO2:JO4674 to A:A

make them all range from 2 to 4674, for best efficiency.

This: "formula omits adjacent cells" is just a warning to advise you that your formula may be missing a column/row of info.. you can just ignore if you know you have covered the ranges you want.

Make sure that all the items that are supposed to match your criteria are exact matches. I.e. that there are no leading or trailing spaces in any cells, etc..

also, ensure that your ranges actually covers the whole lookup table and you aren't short a few rows...

Originally Posted by michaelweaver4

.....it's off by only a few and my formula is giving me a number that is just shy of what is correct based on my manual calculation.

If any of the numbers are text-formatted they may not be included in the MAX totals, you can check that by using ISNUMBER function, e.g.

=ISNUMBER(A1)

will return TRUE only if A1 contains a "true" number

Can you post a sample worksheet showing the disconnect?

I mean attach a sample workbook here.

You have to confirm the formula with CTRL+SHIFT+ENTER not just ENTER so that { } brackets appear around it... then you will see 6.