Monthly geomean with month and year criteria

Hi.
I'm trying to find monthly geomean of the year 2008-2018 and dates are different. I found monthly arithmetic mean of returns by averageifs function by month, year criteria. however couldnt find geomean this way and any other way in these forum..It just calculates the mean of whole range and ignores the criterias, I guess. I have very large file so i wanted a function with less manual operations and click down entire area.

Is there any way to find geomean with year and month criteria?

=IF(AND($A$2:$A$1254=YEAR(C2);$B$2:$B$1254=MONTH(C2));0;GEOMEAN($J$2:$J$1254)-1)
=GEOMEAN(IF(AND($A$2:$A$1254=YEAR(C2);$B$2:$B$1254=MONTH(C2));($J$2:$J$1254)-1) ctrl+shift+enter
'and' function doesnt work with range frormulas i guess. Then how to add multiple criteria? IFS didnt work or i missed something.

The issue seems to be how you defined your array formulas. First of all, I took the liberty of converting the data in your data range into an Excel table. In this case, it was mostly a matter of convenience since you can address table columns by their name rather than cell references. This makes it easier to tell what the formula is doing. Tables also have a lot of other advantages: http://www.utteraccess.com/wiki/Tables_in_Excel

So the monthly Geomean is: =GEOMEAN(IF([Year]=[@Year],IF([Month]=[@Month],[value],FALSE))) as an array formula.

One trick to constructing an array formula of this type is to pretend you are working with just a single row and develop an IF statement that yields TRUE or FALSE. In this case we could make an if statement =AND (year=year,month=month) but that won't work for an array formula. To make the analogy, you need to nest the if statements. =IF(year = year, IF(month=month, Use Range, False)).

I like to call out the False explicitly. You don't have to do that since the default is if the IF statement fails, then the result defaults to False.

@azopt.... Array-enter (press ctrl+shift+Enter instead of just Enter), as you indicated:

=GEOMEAN(IF($A$2:$A$1254=YEAR(C2);IF($B$2:$B$1254=MONTH(C2);$J$2:$J$1254))) - 1
or
=GEOMEAN(IF($A$2:$A$1254=YEAR(C2);IF($B$2:$B$1254=MONTH(C2);1+$E$2:$E$1254))) - 1

We cannot use AND in an array-entered formula because the AND function "swallows" its array parameter "whole", since that is its normal behavior. In other words, array-entered AND($A$2:$A$1254=YEAR(C2);$B$2:$B$1254=MONTH(C2)) returns a single TRUE or FALSE result based on the array of conditions (i.e. TRUE if all of the rows have the year and month), instead of the row-by-row TRUE or FALSE result that you intend (i.e. TRUE if that row has the year and month).