# Sum if Values Match



## SoCalGal (Dec 10, 2022)

What formula can I use in Column D?


----------



## HongRu (Dec 10, 2022)

Try in D1.

```
=IF(A1="","",SUMPRODUCT(($B$1:$B$7=A1)*$C$1:$C$7))
```


----------



## Alex Blakenburg (Dec 10, 2022)

Typically Row 1 would be a heading and not the 1st row of data.  If that is the case or you can live with that, this might work for you.

Book2ABCD1GrpGrpRowsAmtSub-Totals20010015.0030.00300110.00 400200115.00 500300220.0020.00600325.0055.00700330.00 800400435.0035.00Sheet1Cell FormulasRangeFormulaD2:D8D2=IF(B2<>B1,SUMIFS($C$2:$C$8,$B$2:$B$8,B2),"")


----------



## SoCalGal (Dec 11, 2022)

HongRu said:


> Try in D1.
> 
> ```
> =IF(A1="","",SUMPRODUCT(($B$1:$B$7=A1)*$C$1:$C$7))
> ```


I don't understand why * is in the formula, but it works.  Thank you!!!


----------



## Alex Blakenburg (Dec 11, 2022)

SoCalGal said:


> I don't understand why * is in the formula, but it works.  Thank you!!!


In its standard form SumProduct uses a comma to seperate the arrays, which it will then multiply by each using the corresponding row.

So on the face of it, you would expect it to look like this:

```
=SUMPRODUCT( $B$1:$B$7=A1, $C$1:$C$7 )
```

You will find that, using that will produce 0.
This is because in its standard form the function does not convert the result of $B$1:$B$7=A1
which is "{TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE}" to 1s and 0s and treats these as text.

Using the standard form to overcome this you need to apply an arithmetic operator to the TRUEs and FALSEs, to convert them to numeric 1s and 0s.
eg 1 * TRUE = 1 or 0 + TRUE = 1 and what is probably the most common   --TRUE = 0 (double negative or Unary operation)
So this will work.

```
=SUMPRODUCT( --( $B$1:$B$7=A1 ), $C$1:$C$7 )
```
or 

```
=SUMPRODUCT(1 * ( $B$1:$B$7=A1 ), $C$1:$C$7 )
```

Rather than doing that many opt for the format of using multiply rather than the comma format, hence the * in the formula.

```
=SUMPRODUCT( ($B$1:$B$7=A1) * $C$1:$C$7 )
```

The multiply in the one operation converts the TRUEs & FALSEs to 1s and 0s and then Multiplies it against the next array(s) the same as if there was a comma.
This method is also more flexible.
For a visual explanation see Leila's video below from the 2min to 5.5min mark.

PS: If there is an equivalent SUMIFS or COUNTIFS option, they tend to be more efficient than SUMPRODUCT. I should have followed HongRu's lead though and gone with the below which doesn't need the Heading row.

```
=IF($A1="","",SUMIFS( $C$1:$C$7, $B$1:$B$7, $A1 ) )
```


----------



## SoCalGal (Dec 18, 2022)

Alex Blakenburg said:


> In its standard form SumProduct uses a comma to seperate the arrays, which it will then multiply by each using the corresponding row.
> 
> So on the face of it, you would expect it to look like this:
> 
> ...


Thank you so much for taking the time to explain this.  Very helpful!


----------



## Alex Blakenburg (Dec 19, 2022)

No problem. Glad that you found it helpful.


----------

