Sum with multiple criteria and first matches only (excluding duplicates)

Question

I am trying to sum column D for the first match with column A = "green" & column C = "CA", so excluding duplicates for the combinations in A:B. The total should be 200.

company	ee	state	amount
green	1234	CA	100
green	1235	CA	100
yellow	1236	GA	100
green	1234	CA	100

SUMIFS doen't work and I was thinking I could use SUMPRODCUT. But something still isn't working... =SUMPRODUCT(--(A2:A11="green"),--(C2:C11="CA"),D2:D11) results in 300.

Answer 1

Some suggestions:

(1) Groupby

=SUM(GROUPBY(A2:B5&"",D2:D5,LAMBDA(c,@c),0,0,,(A2:A5="green")*(C2:C5="CA")))

or

(2) Map

=SUM(D2:D5*(MAP(A2:A5,B2:B5,C2:C5,LAMBDA(a_,b_,c_,COUNTIFS(A2:a_,"green",B2:b_,b_,C2:c_,"ca")))=1))

or

(3) Xlookup

=LET(u,UNIQUE(FILTER(A2:B5,(A2:A5="green")*(C2:C5="CA"))),SUM(XLOOKUP(TAKE(u,,1)&TAKE(u,,-1),A2:A5&B2:B5,D2:D5)))

---

Notes

(1) Groupby

You can almost do all of the calculation in the Groupby, where the @c is used to include only the first element of each group. The output of the Groupby if you include the total is

Row Field 1	Row Field 2	Value 1
green	1234	101
green	1235	102
Total		101

so you can't use the total directly because it only applies to the first group. I have chosen to make the first two columns of the table strings by using &"" before summing the table so that only the last column is used in the sum.

(2) Map

The map includes a Countif that counts how many matching rows exist up to and including the current row. If the result is one, the current row must be the first one in the group.

(3) Xlookup

Probably the most obvious way to do it is to get the unique values for the first two columns where the first and third columns match "green" and "CA", then do a lookup to get the first matches for each unique pair of values. In general there should be a delimiter between column A and B when concatenating them to avoid "cat + hat" type matches, but since one is a string and the other numeric this shouldn't happen. It's not clear to me whether the first two 'keys' (columns A and B) define a row uniquely - arguably you should filter before doing the lookup which would give you

=LET(u,UNIQUE(FILTER(A2:B5,(A2:A5="green")*(C2:C5="CA"))),SUM(XLOOKUP(TAKE(u,,1)&TAKE(u,,-1),FILTER(A2:A5&B2:B5,(A2:A5="green")*(C2:C5="CA")),FILTER(D2:D5,(A2:A5="green")*(C2:C5="CA")))))

All the above formulas could be written more generally.

company	ee	state	amount	Groupby	Map	Xlookup
green	1234	CA	101	203	203	203
green	1235	CA	102
yellow	1236	GA	103
green	1234	CA	104

I have changed the values in column D slightly to make it clearer which rows are being summed.

Answer 2

Cell F1 company
Cell G1 ee

Formula in H1

=LET(filt,SORT(FILTER(A2:D5,(A2:A5=F1)*(G1=C2:C5)),{1,2},,FALSE), SUM(IF((CHOOSECOLS(filt,1)=CHOOSECOLS(DROP(VSTACK({"",""},filt),-1),1))* (CHOOSECOLS(filt,2)=CHOOSECOLS(DROP(VSTACK({"",""},filt),-1),2)),,CHOOSECOLS(filt,4))))