Sum multiple values based on matches

New Contributor

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

@rrbailey89 

Use SUMIFS().

 

@rrbailey89 

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!

 

 

@Peter Bartholomew , why not SUMPRODUCT()?

@Sergei Baklan 

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!

@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.

@Sergei Baklan 

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.

@Hans Vogelaar , thank you