Forum Discussion
Filtering by named range
JacobWilcox One possible formula you could try is the FILTER function with ISNUMBER / XMATCH criteria on the employee names, then AVERAGE the results:
=AVERAGE(FILTER(TableHours, (TableDate>=NinetyDaysAgo)*(TableDate<=TODAY())*ISNUMBER(XMATCH(TableEmployee, Technicians))))Please note, I don't use Google Docs, so I'm not 100% sure how this formula will translate over to that platform. According to the official Google Docs support page for the FILTER function, the syntax appears to be slightly different than Excel, so you may be able to separate each condition with commas instead of asterisks. I also don't know if the XMATCH function will handle a range of values in the same manner as Excel to generate the desired array of results.
JacobWilcox One possible formula you could try is the FILTER function with ISNUMBER / XMATCH criteria on the employee names, then AVERAGE the results:
=AVERAGE(FILTER(TableHours, (TableDate>=NinetyDaysAgo)*(TableDate<=TODAY())*ISNUMBER(XMATCH(TableEmployee, Technicians))))
Please note, I don't use Google Docs, so I'm not 100% sure how this formula will translate over to that platform. According to the official Google Docs support page for the FILTER function, the syntax appears to be slightly different than Excel, so you may be able to separate each condition with commas instead of asterisks. I also don't know if the XMATCH function will handle a range of values in the same manner as Excel to generate the desired array of results.
djclements , that got me the result that I needed, thanks! The XMatch-IsNumber combination is what I was missing. The formula you provided me worked just fine in GoogleSheets without any needed changes. I appreciate your help.