[SOLVED] SUMPRODUCT or SUMIF with Arrays

SUMPRODUCT or SUMIF with Arrays

Does anyone know of a streamlined way of SUMPRODUCT or SUMIF with an array? Basically want to say if not in this list of multiple criteria then sum. See screenshot.

=+SUM($O3:$Q3)+SUMPRODUCT(--(($D$2:$L$2<>$O$2)*$D3:$L3)) works but want to take that $0$2 and make it multiple criteria without having to add additional SUMPRODUCT's for c_prob and d_prob

Solution

Match Multiple Criteria

The formula you're looking for in cell R3 is:

=SUM($O3:$Q3)+SUMPRODUCT(ISNA(MATCH($D$2:$L$2,$O$2:$Q$2,0))*$D3:$L3)

In Excel versions after 2019, you don't have to use CSE for array formulas anymore. You can use the SUM function instead the SUMPRODUCT function and you can use the newer XMATCH function:
```
=SUM($O3:$Q3)+SUM(ISNA(XMATCH($D$2:$L$2,$O$2:$Q$2))*$D3:$L3)
```

The Issue

To return TRUE/FALSE-s for a single-criteria mismatch, as you have shown, you can use:
```
=$D$2:$L$2<>$O$2
```
For multiple mismatches, you have concluded that you cannot use:
```
=$D$2:$L$2<>$O$2:$Q$2
```
Keep in mind that you can use it when the sizes of the arrays are the same with a different functionality.
Instead, you can use a combination of the ISNA and MATCH functions:
```
=ISNA(MATCH($D$2:$L$2,$O$2:$Q$2,0)
```
The following screenshot illustrates some of it. Keep in mind that this is in MS365, so some of the features may not be available in your Excel version.