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 ...
excel
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!

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Jul 26, 2022

    PeterBartholomew1 , 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.

    • HansVogelaar's avatar
      HansVogelaar
      MVP
      Jul 26, 2022

      SergeiBaklan 

      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.