averageifs dynamic criteria ranges

Hi,

Would really appreciate if anyone could help me out here.

I've got a problem with an averageifs formula where I need a certain degree of flexibility when it comes to the number of criteria.

This is an example of how the original formula I use might look like averageifs(A1:A31,B1:B31,">2",....).

Now, one of the fields in the data snapshot - Column C, specifically range C1:C31 contains days - from 1 to 31. So the existing formula calculates the averages for all days. The user's now asked me if he could see the the data for a specific day, something like he enters '12' and the report only shows the data for that day.

So, I rewrote the formula averageifs(A1:A31,B1:B31,">2",.... ,C1:C31,IF(J1="","*",J1)) to be able to calculate the averages for all days or any specific one. J1 is the cell for a day value and is empty if for all days.

The problem is that damn formula doesn't work. It works if I change the day value into text but not when it's a number.
I don't want to change it into text because the user might create a separate report and enter the days as numbers in which case the formula is going to stop working.

Could really use some help here.

Thanks in advance.

Thank you folks for your help. It works like a charm.