New Contributor

# Criteria check then calculation (sumif or If Then?)

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

# Re: Criteria check then calculation (sumif or If Then?)

@Pdoshi100 Try with SUMPRODUCT like this:

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

# Re: Criteria check then calculation (sumif or If Then?)

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

# Re: Criteria check then calculation (sumif or If Then?)

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.

# Re: Criteria check then calculation (sumif or If Then?)

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