Offset Match? Find something in 1st row, search in column, sum product diff column

TaDecide.xls

I have attached a file and would appreciate any help that I can get.

I was told to use this formula =SUMIF(OFFSET(A1, 1, MATCH("Sector", 1:1, 0)-1, 3000), "Public", INDIRECT(L2:L&"Public"+1))/COUNTIF(OFFSET(A1,1,MATCH("Sector", 1:1, 0)-1, 3000), "Public")


I have uploaded the file with which I am working (though i need to do it on multiple files). Could you please help me find the Sector (G), and sum column L if the value in sector is "public"?

Thank you very much for any help that you can provide

Im sorry that I didn't explain it clearly enough. I am trying to look in the first row and find "Sector", then within Sectors column, find all of the times that the word "Public" is used and pull from the column that has "average course cost", every time that the word public is used. For example, not using sector and public, this is what I want to do

I have a workbook of data in excel and I am trying to look for a word "Pluto" in the first row of the document, then I want to search within "Pluto"'s column and look for the word "Blue" which will occur multiple times in the column. I then want to get an average of numbers in another column for each row that blue occurs in.

So it would look like

a b c d e pluto f g
x x x x x blue x 20
x x x x x yellow x 40
x x x x x blue x 29
x x x x x blue x 30


I want to search for "pluto", find all of the "blue" within that column, and pull the numbers associated with "blue" (20, 29, 30) and average them.

Thank you for your help. Im not sure exactly what your code is doing? I am trying to just get a number (average) in one cell. Can you explain what this is doing and where the answer is?