Show average of cells which display X text.

Hi philipo51617,

My recommendation would be to use a pivot table and VLOOKUP for this.

Start by selecting your data and inserting a pivot table in a new sheet. I'd use your column D data for the rows and an average of column L. By default, pivot tables use the sum, so you will need to update the Value Field Settings to average.

In column N, I would enter a VLOOKUP function. I made a very simplified version of your data in the screenshot below, but essentially:

• Lookup value: your group rate name which is in my column A, your column D
• Table array: I selected the fields from my pivot table (be sure to add $ before the row & column to hold the function to just those fields when you copy & paste the formula)
• Col Index #: the averages are found in the second column, so I entered 2
• Range lookup: I want an exact match, so I entered false

I hope this helps you accomplish what you're wanting to do!