Criteria check then calculation (sumif or If Then?)

Copper Contributor

Hello, trying to do the following:

If cell in range A is equal to "x" then multiply cell in range B * Cell in range C then add all the results to produce one number.

Attempted: =sumif (c2:c117,"x",=d2:d117*e2:e117)

Is that a sumif formula or If then? Or must I create a column to populate the multiplication results and then do a sum formula in the final destination?

 

Thank You.

Priya

4 Replies

@Pdoshi100 Try with SUMPRODUCT like this:

=SUMPRODUCT(--(C2:C117="x"),D2:D117,E2:E117)

 

Many thanks that did the trick @Riny_van_Eekelen. Question: what is the purpose of the 2 dashes just inside the first parenthesis?

 

It will convert the list to TRUE or FALSE to evaluate the result. You can use "Evaluate Formula" feature to examine the result. You can find if under Formulas ribbon --> Formula Auditing group --> Evaluate Formula.

@Pdoshi100 The part between the brackets produces and array with TRUE and/or FALSE. The "double" dash transforms the TRUEs to 1 and FALSEs to 0. Then SUMPRODUCT can perform the desired calculation. However, I omitted to mention that the following formula should also work (note that the ranges multiplied in stead of being separated by commas).

=SUMPRODUCT((C2:C117="x")*D2:D117*E2:E117)

 More about this in the link below:

https://support.microsoft.com/en-us/office/sumproduct-function-16753e75-9f68-4874-94ac-4d2145a2fd2e