Help with Excel formula (Unfortunately there is no function called MAXIF)

[Petal]

Can anyone help, I want basically the same thing only with the return of the maximum value

=SUMIF(FE7:FE89,"Beverage",D789)

So I have two columns (FE7:FE89 - this one has 3 different labels and D789 - this has the values) and I want excel to return the maximum value for all values labelled beverage. Unfortunately there is no function called MAXIF, but surely there must be a way around this somehow?

Any help greatly appreciated.

 

[Nairb]

If you add in an extra column beside FE and use an if statement to populate the total if where the cell in FE = 'Beverage' =if(FE7="Beverage", D7,0)
That should mean that in any rows with Beverage, the amount will be included, for others it will be 0
Then just sum the new column.

 

[Crugers]

can anyone help, i want basically the same thing only with the return of the maximum value

=sumif(fe7:fe89,"beverage",d7:d89)

so i have two columns (fe7:fe89 - this one has 3 different labels and d7:d89 - this has the values) and i want excel to return the maximum value for all values labelled beverage. Unfortunately there is no function called maxif, but surely there must be a way around this somehow?

Any help greatly appreciated.

Create a User Defined MAXIF() Function!


See http://www.bettersolutions.com/excel/EIK284/LX317911811.htm

And if you are not sure how to do it, see
http://www.vertex42.com/ExcelArticles/user-defined-functions.html

HTH

 

[orka]

Less elegant but if you don't mind adding an extra column, a simpler way would be to add an extra column which only shows the beverage values and then find the max of that. So the formula in the new column would be (starting at the top and copying down), =if(fe7="beverage",d7,0) - copy this down to row 89 and then at the bottom, find the max of the values.

 

[Petal]

Thanks for all your help, what crugers suggested is exactly what I wanted, and it worked like a treat! Many thanks.