I have an excel problem need help with. I have two columns in excel like below, one with numeric values and the second with some sort of option IDs. I am trying to compare the sum of the matching options and print the smallest value and option ID. So formulas should print 3 and O1 in the below case. The complexity I have here is that I don't know how many options eventually there will be. So I can't do it by factoring all the option IDs in a formula to compare and print results. Additionally, there are some other values in the ID column, such as x(not an option, should be excluded) and blank cells.

2 / O1 
4 / O2 
1 / O1 
1 / x
4 / O3 
2 / -
5 / O2

CodePudding user response：

You could try:

Formula in D1:

=LET(X,FILTER(CHOOSE({1,2},SUMIF(B1:B7,B1:B7,A1:A7),B1:B7),COUNTIFS(B1:B7,B1:B7,B1:B7,"<>x",B1:B7,"<>""")>1),INDEX(SORTBY(X,--INDEX(X,,2)),1))

CodePudding user response：

Perhaps, you may try as shown below, formulas used applicable to MS365 users only

• Formula used in cell C1

=LET(array,FILTER(A1:B7,ISNUMBER(B1:B7*1)*(B1:B7<>"")),
id,INDEX(array,,2),
u,UNIQUE(id),
sumf,BYROW(u,LAMBDA(x,SUMIFS(A1:A7,B1:B7,x))),
FILTER(CHOOSE({1,2},sumf,u),sumf=MIN(sumf)))

And when writing this formula if you have enabled the Beta Channel from Office Insiders then you can use the HSTACK() in place of CHOOSE()

• Formula used in cell C1

=LET(array,FILTER(A1:B7,ISNUMBER(B1:B7*1)*(B1:B7<>"")),
id,INDEX(array,,2),
u,UNIQUE(id),
sumf,BYROW(u,LAMBDA(x,SUMIFS(A1:A7,B1:B7,x))),
FILTER(HSTACK(sumf,u),sumf=MIN(sumf)))

Following Excel functions used to derive the Output,

• LET() -- Used to assigns names to calculation results,

• FILTER() -- Used twice, in the beginning to create an array of results which excludes the non numeric as well as the blanks, and at last to get the required output,

• ISNUMBER() -- Used within FILTER() to get numeric values, returns TRUE,

• INDEX() -- Used to extract the ID's from the array,

• UNIQUE() -- Used to get the unique values from the id,

• BYROW() & LAMBDA()-- Used to apply a LAMBDA() to each row and to return an array of the sum of unique values,

• SUMIFS() -- Used with in LAMBDA() to get the sum

• CHOOSE() / HSTACK() -- Used to return the array formed by appending each of the array arguments in a column-wise fashion,

• MIN() -- Used to return the min of sum values.