Criteria check then calculation (sumif or If Then?)

Question

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?&nbsp;Thank You.Priya

riny_van_eekelen · Answer

Pdoshi100&nbsp;Try with SUMPRODUCT like this:=SUMPRODUCT(--(C2:C117="x"),D2:D117,E2:E117)&nbsp;

pdoshi100 · Answer

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

kmohanreddy20051735 · Answer

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 --&gt; Formula Auditing group --&gt; Evaluate Formula.

riny_van_eekelen · Answer

Pdoshi100&nbsp;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)&nbsp;More about this in the link below:https://support.microsoft.com/en-us/office/sumproduct-function-16753e75-9f68-4874-94ac-4d2145a2fd2e&nbsp;

Forum Discussion

Criteria check then calculation (sumif or If Then?)

4 Replies

Resources