I have a source table, now need to column according to the model number and specifications for the value of the column (value have more than one, may be repeated) and specifications in new form the next column to find the minimum value of the same model specification, under a list of the second value, the third small value, and so on, the function is how to do it, can consult, explain in detail the best

CodePudding user response:

A, B, C, E, F
Number 1 specifications specifications
1 a 60 1 a 2
3 1 a 60
4 1 b 70
5 2 b 70
6 2 c 80
7 3 c 80
8 3 d 80
For type 1, the specification is the SUM of the number of a [array at the same time, press CTRL + shift + enter computing]

=SUM (IF ((A2: A8=E2) * (B2: B8=F2), C2: C8)) 

the IF and meet A2: A8 models=1 and B2: B8 specifications=a corresponding cell C2: the two conditions of C8 value, an array of reoccupy SUM SUM


For type 1, the specification of a minimum, the second and the third small for [array at the same time, press CTRL + shift + enter computing]

=IFERROR (SMALL (IF ((A2: A8=E2) * (B2: B8=F2), C2: C8), 1), "") 
=IFERROR (SMALL (IF ((A2: A8=E2) * (B2: B8=F2), C2: C8), 2), "") 
=IFERROR (SMALL (IF ((A2: A8=E2) * (B2: B8=F2), C2: C8), 3), "") 

If the array of meet the conditions, the same with SMALL (array, k) for the first k minimum, when k> array was SMALL returns # NUM! Error value, so the error values with the IFERROR empty