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 a 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 really go about it by factoring all the option IDs in a formula to compare and print results.
2 / O1
4 / O2
1 / O1
4 / O3
5 / O2
CodePudding user response:
With Office 365 we can use a LET with UNIQUE, SUMIF and FILTER:
=LET(num,A1:A5,
id,B1:B5,
unq,UNIQUE(id),
smf,SUMIFS(num,id,unq),
FILTER(CHOOSE({1,2},smf,unq),smf=MIN(smf)))
If one has it the CHOOSE can be replaced with HSTACK:
=LET(num,A1:A5,
id,B1:B5,
unq,UNIQUE(id),
smf,SUMIFS(num,id,unq),
FILTER(HSTACK(smf,unq),smf=MIN(smf)))