maxif function in vba

  • Hi
    I want to write a reuseable maxif function in vba. The following code is working when entered directly into the cell:

    = SUMPRODUCT(MAX((B1:B6=B2) * (A1:A6)))

    Now I want to pack this function into vba code to make it commonly available. I tried it with the following code but it doesn't work. I assume there is a problem with the passing of the range parameters. I also tried it with String and Variant as parameter types - without success. Can anybody help me?

    Public Function MaxIF(criteriaRange As Range, searchValue As Variant, calcRange As Range)
    AciveCell.Formula = "=SumProduct(Max((criteriaRange = searchValue) * (calcRange)))"
    End Function
  • Re: maxif function in vba


    After wanting these functions for years I finally wrote these last month. They use the same structure as SumIf and CountIf functions (actually they use these functions to evaluate the function.)



  • Re: maxif function in vba

    Hi mienz,

    You need to break the variables away from the text within the formula.

    Public Function MaxIF(criteriaRange As Range, searchValue As Variant, calcRange As Range)
        ActiveCell.Formula = "=SumProduct(Max((" & _ 
    criteriaRange.Address & "=" & searchValue & ") * (" & calcRange.Address & ")))"
    End Function

    You may also want to use relative reference in your function as it current uses absolute.

    PS. Don't forget to use the code tags


  • Re: maxif function in vba - additional functions


    And it should be a simple matter to extend the set to include averageif, as well as versions that ignore blank/empty cells (sumaif, maxaif, minaif, averageaif) ... ;)

    ... now attached (below). BTW, shouldn't the original code check to see that BOTH ranges have only one area (not either/or) ?

  • Re: maxif function in vba - additional functions


    The code does not deal with areas but simply checks that the two ranges have the same number of columns and rows, that is they are identically sized. If a range has multiple areas then the function will only check the first area of each range as this is teh default area returned.

    My use of the functions was always on contiguous cells- ie ranges with only 1 area.

    It might be worth trying to write a generic function that has a parameter passed that selects the actual function and comparison to be made. This would use a select statement based on that parameter.

    Note that the code proposed by Andy Pope would be much more efficient and if I get time I will write a generic one based on his approach.



  • Re: maxif function in vba - additional functions

    Hi, and thanks for the reply.

    Now I understand why you used 'or' in your check for areas.

    On the code suggested by Andy Pope, this would be a great speedup for a MaxIf function that only had to deal with positive numbers (e.g. returning maximum of a range of dates), but it has the problem described in when it comes to negative values. There's a similar problem with MinIf using that method. And of course the condition part of the formulae doesn't use the same conditions as CountIf and SumIf.

    I think I like your code better (even if it is slower). And it's possible to avoid some overhead by testing the rngEvaluate with Countif first to see if any cells meet the condition, avoiding the iterations if none do, as in:

    BTW, I built on your code and added AverageIf, plus a few functions for Sum, Min, Max and Average that will ignore non-numeric values (all named with "..AIf" at the end).

    Thanks again for the basis!!

  • Re: maxif function in vba

    Hi Alan,

    Can you perhaps adapt the code to be able to have tow condidtions for the maximum. That is, I'd like to have something like this:
    Maxif(rng1, condition1, rng2, condition2, rngValues)
    where rngValues could be the same as rng1 or rng2.

    Best regards

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!