Oct 17 2021 10:08 AM
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.
Oct 17 2021 11:12 AM
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)
Oct 17 2021 11:22 AM
Oct 17 2021 04:10 PM
Oct 17 2021 10:40 PM