Can sumif and Max functions combined into one?

**ReconMan** · 09-20-2011, 10:51 AM

Let's say I have a product with different category. I want to be able to pick the product with the highest member count value. At the same time, there are other products with the same category. In this case, I can do a Sumif function to add the total member count value. Because I have products with different category and products with same category in the same worksheet, it's diffifult to built a function that would do both. I attached a document for example purposes. Any thoughts on this? Thanks!

**daddylonglegs** · 09-20-2011, 10:56 AM

There's no MAXIF function as such but you can use an "array formula", e.g.

=MAX(IF(A2:A7="AB",C2:C7))

The formula needs to be confirmed with CTRL+SHIFT+ENTER so that curly braces like { and } appear around the formula in the formula bar

**ReconMan** · 09-20-2011, 11:12 AM

I'll just have to sort the product with different category and add the above function and sort the product with same category and add the sumif function. Thanks for your help!.

**daddylonglegs** · 09-20-2011, 11:23 AM

Perhaps I'm not clear about what you want to achieve but I don't see the need for sorting - did the formula I suggested work or is it more complex than that?