Average for Conditional Criteria

Hello,

I have a condition specific to COUNTIF and SUMIF.

In the attached spreadsheet, the desired result is inputted in column "E" and "F".

Basically, for the multiple condition here is to check for repeat values in "Test ID" and "Test Set" (simultaneously).

For example,

For "ADDERT THREE" its repeated thrice. Twice against "Test Set 2" and once against Test Set 1". So in the "Unique count" column for "ADDERT THREE" the desired value should be split against the test set. Also, the column C is populated based on "COUNTIF", hence the unique value for the "Test ID" is 2 i.e. in case of "ADDERT THREE" the unique value is 2 and this is divided by the number of times it is repeated against the test set. In this case, the unique value is 2 and "ADDERT THREE" is repeated thrice against two unique test set.

Also the unique value should repeat only once against the test id.

Here is the logic - "ADDERT THREE" is repeated thrice against "TEST SET 1" and TEST SET 2". and the unique count is 2. So the logic should divide 2 by the number of times (unique) the test id is repeated i.e. by 2 as the unique repetition is 2 and the value 1 should be populated against the first test set which is 1 for "Test Set 1" and 1 for "Test Set 2". Also, the third repeat value of "Test ID" should be 0.(in case it is repeat against the same "test set")

For Unique sum, it needs to follow the same logic but only difference is it should sum.

Similarly, for "ADDERT SIX" unique count should be 1.66 which is 5 divided by 3( no of times test id is repeated uniquely against three unique Test set - Test set 1, Test set 2 and test set 3) which is around 1.66. Same logic for sum which would be 450/3 2 which is 150.

Also, if there is no complications of repetition in terms of test id, it should return the same value which is in column C.

I have tried the logic of COUNTIFS and SUMIFS simultaneously with AVERAGEIFS but I could not get the desired value.

I appreciate and thank all the help I can get on this.

Thanks