I have two excel sheets where I need to match three values to return a fourth. The similar columns are month, agent, and subdomain. The fourth column is called difference.
See Question&Answers more detail:osI have two excel sheets where I need to match three values to return a fourth. The similar columns are month, agent, and subdomain. The fourth column is called difference.
See Question&Answers more detail:osConcatenate would work, as per @MakeCents suggestion, but if you don't want a helper column, SUMPRODUCT
would work.
example:
=SUMPRODUCT(--(A2:A12="d"),--(B2:B12="S"),--(C2:C12="Apr"),D2:D12)
would search range A2:A12 for "d", B2:B12 for "S" and C2:C12 for "Apr", and return the value fom D2:D12 that corresponds to where all 3 are true. If multiple lines match, it will add the value in D2:D12 for all matching rows.
The --
is used to change the True/False results into 0 and 1 for use in multiplication
Limitations of SUMPRODUCT