Populating data based on one criteria from a database for excel 2016

[choongko]

Need help on the formula for excel 2016 to extract data from a worksheet based on one criteria.  In the sample data file, there are two tables.   In table 1, the objective is to populate the table by extracting the data from the Taskscore Wksheet, based on the criteria of 'C4'.    

For table 2, reference to the populated data in table 1, compute the average score based on Functional Groups (FG) and the taskID in the respective 'M' columns.   

There are two examples of the  outcome of the two populated tables.  Appreciate your advice in advance. 

[#2]

@choongko 

In order to populate all datasets for one component selected in cell C2 i suggest advanced filter. If you worked with office2021 you could use filter function.

TaskScore!$A$2:$E$251   is the List Area for advanced filter and C1:C2 is the criteria range for advanced filter. Please note that in C1 "Component" is entered without ":".

In the attached file you can see my entries in the advanced filter.

In order to calculate the averages for Functional Groups and TaskIDs i suggest to enter formula below in cell C6 and to copy across range C6:E19. To apply this formula you have to change the headers of table1 to "M-1", "M-2" and "M-3". This would be more convenient than headers "M-1 Avg. Score" , "M-2 Avg. Score" and "M-3 Avg. Score".

=SUMIFS(TaskScore!$E$3:$E$251,TaskScore!$B$3:$B$251,C$5,TaskScore!$C$3:$C$251,Sheet1!$B6)/COUNTIFS(TaskScore!$C$3:$C$251,Sheet1!$B6,TaskScore!$B$3:$B$251,C$5)

[#3]

@choongko 

Do it with Pivot tables and slicer.

See attached file.

[#4]

Thanks the solution. Unfortunately, I have to do it with ver 2016 as my office is still using it. Do you have any solutions for that?

[#5]

Pivot tables also work in Office 2016. Maybe slicers don't but they are only for comfort.