Filtering by named range

[JacobWilcox]

NOTE: I'm building this in GoogleSheets as per my company's accessibility requirements. Most formulas work similarly to Excel, but not all. Any errors in the Excel version that I added to this post are because of the transition between GoogleSheets and Excel.

I'm using the below formula to give me a rolling 90-day average of our employees' "hours".  

=AVERAGEIFS(TableHours, TableDate, ">="&NinetyDaysAgo, TableDate, "<="&TODAY())

Each named range here that begins with "Table" refers to a master table into which daily data is entered. There is also a range in the table for the employee's name (TableEmployee).

Our master table includes data for both technicians and service advisors, two distinct groups of employees. I need to adjust the above formula to find the rolling average for only our technicians (i.e. disregard any data for an advisor/only include data for a technician). I have a table that lists our technicians and advisors, and have created named ranges for each as "Technicians" and "Advisors", respectively.

What would be the simplest way to adjust my formula to give me the rolling average for only the technicians? I've been using chat GPT to try and find ways to make it work, but every suggestion seems way too convoluted and inefficient. It's not that complicated of a question, so I figure the answer has to be fairly simple.

 

[#2]

Attach the sample file to your post so that we can check named ranges.

[#3]

The file is now attached. Any errors in any cells are due to the switch from Google Sheets to Excel.

[#4 · Accepted solution]

@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.

[#5]

@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.