Function works on msgbox but not in cell formula

[lipanook]

I've written a function to calculate the geometric mean of a set of data that fit certain criteria (similar to the native averageif() or sumif() functions). It works if I use a test MsgBox, but I can't get it to work as a formula in a cell. The formula I've written is:

=geomeanif(M:M,A:A,V2)
​

The result is #VALUE !

If I try it in a debugging subroutine, it works well:

[INDENT][FONT=arial][FONT=courier new][/FONT][B][I][U][SUB][SUP]<strike>
</strike>[/SUP][/SUB][/U][/I][/B][FONT=courier new]Sub test()
    MsgBox geomeanif(Range("m:m"), Range("a:a"), "Test Criteria")
End Sub<strike></strike>[/FONT]
[/FONT][/INDENT]

This gives me a sensible double as a result. I can't figure out why it works in the MsgBox but not the cell formula. What am I missing here?

Here is my function:

[INDENT][FONT=courier new]Public Function geomeanif(geomeanRange As Range, criteriaRange As Range, criteria)[/FONT][/INDENT]
[INDENT][FONT=courier new]    FirstRow = geomeanRange.Cells(1, 1).Row 'Set first row of data range
    LastRow = geomeanRange.Cells.SpecialCells(xlCellTypeLastCell).Row 'Set last row of data range
    
    LogSum = 0
    dataCount = 0
    
    'Loop through rows in the data range. If the value in the criteriaRange column is the same as the criteria, then add the log10 value of the geomeanRange column cell to the cumulative sum.
    For i = 1 To LastRow
        If criteriaRange.Cells(i, 1).Value = criteria Then
            LogSum = LogSum + Log10(geomeanRange.Cells(i, 1).Value)
            dataCount = dataCount + 1 'count the number of data that match the criteria
        End If
    
    Next i
    
    'Calculate geomean
    geomeanif = 10 ^ (LogSum / dataCount)[/FONT][/INDENT]
[INDENT][FONT=courier new]End Function[/FONT][/INDENT]

_{^{<strike>
</strike>}}

 

[rlv01]

Try replacing this line

    lastRow = geomeanRange.Cells.SpecialCells(xlCellTypeLastCell).Row    'Set last row of data range

with this one

    lastRow = Cells(Rows.Count, geomeanRange.Column).End(xlUp).Row  'Set last row of data range