# Xlookup & SUMIF

#### RehanRao

##### New Member
I have following data:
 Pass # AUDCAD AUDCHF AUDJPY AUDNZD AUDUSD CADCHF CADJPY CHFJPY EURAUD EURCAD 1 23 23 45 -90 -98 78 6 5 3 4 2 98 3 4 5 6 -9 -97 45 345 56 3 -88 98 0 54 56 -32 45 -90 4 34 4 -45 76 -9 -2 -56 33 34 -87 24 4 5 -100 65 -9 -1 45 -44 24 -97 323 55 6 -98 65 -9 -8 46 -55 -90 5 5 645 7 67 -9 -56 9 45 9 -45 -87 345 -90 8 4 -4 -89 9 4 89 -1 9 2 76 9 32 -96 90 8 56 -89 -45 -46 345 -80 10 67 87 -87 7 456 98 -67 -23 57 -88 11 45 64 -45 6 46 90 -56 -11 78 654 12 3 34 57 9 6 -98 -90 -34 -89 43 13 34 67 67 8 5 67 -45 -34 3 3 14 23 78 57 7 5 57 100 -34 3 33 15 -23 -12 -34 -90 -98 90 0 -45 43 -65

Each Pass # have its corresponding profit/loss across currency pair. Foss # 1, AUDNZD & AUSUSD are -ve (-188 is there sum). Pass # 1 profit is 23+23+45+78+6+5+3+4=187 if we dont count -90-98 = -181. For same AUDNZD and AUDUSD pair, pass # 10 has max profit (456+7=463). I want to have a formula probably using Xlookup and SUMIF (or anything else) where is can maximize profit using two passes; one for max profit (using SUMIF(B2:K2,">0") and taking the largest value. for 2nd pass, i need to look at -ve values of currency pair of 1st pass and finding the best profit value for the same Pairs in second pass. How can i do it?