I have this formula that searches within a table for two criteria (F1 & E2 or E3, E4 so on) and returns their corresponding Gallon Value. I just want to know if it is possible that should there be duplicate values in Date & Plate (A4 & A5, B4 & B5) then it should add up their corresponding Gallon values C4 & C5 into cell G3.

This is what i have as of now :

=XLOOKUP($F$1&E3,$A$2:$A$5&$B$2:$B$5,$C$2:$C$5,"",0,1)

CodePudding user response：

Actually you want SUMIFS() function. Try-

=SUMIFS($C$2:$C$5,$B$2:$B$5,E2,$A$2:$A$5,$F$1)

If you want to make it more dynamic then can try-

=LET(x,UNIQUE(B2:B5),y,SUMIFS(C2:C5,B2:B5,x,A2:A5,F1),CHOOSE({1,2},x,y))