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.
=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.
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:
In the final step, SUMPRODUCT will sum all the values in the array that will give us the final result that is 9840.