**THE FORMULA TO SUM IF EQUAL TO ONE OF MANY THINGS**

Although, there is the number of Excel formulas to add/sum hundreds of values. But, what about some specific values from the specific cells like from a list of hundreds of gemstones along with their prices, you have to calculate a bunch of them? While looking for some productive formulas for our readers, we have something fantastic to find the sum of specific values. Let’s start.

**Basic Formula**

`=SUMPRODUCT(SUMIF(range, things, values))`

**Explanation**

You can use SUMIF and SUMPRODUCT functions in a formula to sum values if different cells are equal to one of many things.

**Example**

For the given example, we have used the following formula in H4.

`=SUMPRODUCT(SUMIF(B5:B12,things,C5:C12))`

*Figure 1 – Example of use of SUMIF and SUMPRODUCTS to find the sum of values*

**How This Formula Actually Works?**

Three arguments have been taken by the SUMIF function; range, criteria, and sum_range.

**Range**– B5:B12. We have defined some gems as values for testing purpose.

**Criteria**– E5:E12 range named as *things**. *We can enter up to 8 values in this range to be calculated, however, this range can be expended.

**Sum_range**– C5:C12 containing numeric values (prices in our example).

As we have given more than one criteria to SUMIF, we’ll get multiple results; one for every single value. An array like this will result:

`=SUMPRODUCT ({3300;2000;550;1990;2000})`

In the final step, SUMPRODUCT will sum all the values in the array that will give us the final result that is 9840.

## Leave a Comment