Jul 25 2022 03:53 PM
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.
Jul 26 2022 01:03 AM
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!
Jul 26 2022 01:26 AM
@Peter Bartholomew , why not SUMPRODUCT()?
Jul 26 2022 02:19 AM
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!
Jul 26 2022 05:16 AM
@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.
Jul 26 2022 06:43 AM
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.