Forum Discussion

rrbailey89's avatar
rrbailey89
Copper Contributor
Jul 25, 2022

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. 

excel
  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Jul 26, 2022

    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!

     

     

      • PeterBartholomew1's avatar
        PeterBartholomew1
        Silver Contributor
        Jul 26, 2022

        SergeiBaklan 

        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!

Resources