Forum Discussion
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!
PeterBartholomew1 , why not SUMPRODUCT()?
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!
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.
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.
