0

I am trying to write a formula that adds cell values by matching the reference ID in a separate column. For example, cells G2 and G3 have the same ID, so the amounts in cell H2 and H3 would be added together, and that total, $21.12, would appear in both I2 and I3.

enter image description here

I've been trying to use SUMIF for this, but have only been successful when specifying the exact reference ID in the formula. I need the formula to automatically match based on any Reference ID. Is there any way to use a SUMIF (or another function altogether) to match strings to calculate values?

1 Answer 1

0
  • Use QUERY to SUM Column B and group by Column A
  • VLOOKUPto find totals by Reference ID
  • ARRAYFORMULA to use a single formula for all transaction IDs
  • FILTER limits VLOOKUP only to those rows containing at least one character.
=ARRAYFORMULA(VLOOKUP(FILTER(A2:A,LEN(A2:A)>0), 
    QUERY(A:B, "select A, SUM(B) group by A"),2))

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.