New Contributor

# Sum multiple values based on matches

I have a total of 5 columns of data.

The data is laid out like so:

Column A,B and C are static with C containing values.

Column H and I are static.

What I am trying to accomplish is to insert a formula into Cell J2 that compares H2 and I2 with A2 and B2. If they match then sum all values in C2 that have a corresponding match in A:B.

I hope this makes sense.

7 Replies

Use SUMIFS().

# Re: Sum multiple values based on matches

If you need to use conditions such as

fieldA = fieldH,

they give array results but not range references unless you introduce helper ranges.  SUMIFS etc do not accept arrays as criteria or sum ranges, so separate use of SUM and IF would be required.

``````= SUM(
IF((fieldA=fieldH)*(fieldB=fieldI), valueField)
)``````

It is an array formula but, as a 365 user, I never use anything else!

# Re: Sum multiple values based on matches

@Peter Bartholomew , why not SUMPRODUCT()?

# Re: Sum multiple values based on matches

It would do fine and work with older versions of Excel.  Mind you, I remember first seeing a formula of the form

``````= SUMPRODUCT(
--(fieldA=fieldH),
--(fieldB=fieldI),
valueField
)``````

and being utterly confused!  At first, I couldn't see where multiplication entered the story and the "--" did not achieve anything obvious (a somewhat arcane special notation perhaps).  One gets to take it for granted, even its 'magic' array wrapper property!

# Re: Sum multiple values based on matches

@Peter Bartholomew , don't know why, but I always use it with multiplication

``````=SUMPRODUCT(
(fieldA=fieldH) *
(fieldB=fieldI) *
valueField
)``````

Long ago I knew the difference between these forms, it is in some cases. Bit lazy to dig now where it is.

# Re: Sum multiple values based on matches

Multiplication is required if the ranges don't have the same size. For example, if valueField is a multi-row and multi-column range, fieldA is a single column and fieldB is a single row.

# Re: Sum multiple values based on matches

@Hans Vogelaar , thank you