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))


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


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


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.

RangeB5:B12. We have defined some gems as values for testing purpose.

CriteriaE5: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_rangeC5: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.


Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment