Forum Discussion
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",...
Many thanks that did the trick Riny_van_Eekelen. Question: what is the purpose of the 2 dashes just inside the first parenthesis?
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
- 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.
