How does sumproduct work

We have the cost table for a small company for one month. It is necessary to calculate the total amount spent for January and February for all items of expenditure. For calculating of the costs for the chancellery in January-month, we use our function and specify in the beginning two conditions.

Each of them we enclose in parentheses, and between conditions need to put the «asterisk» sign, meaning the «and» union. We get the following command syntax:. As a result, it turned out that in January 3, dollars were spent for office supplies. Pictures helped. Didn't match my screen. Incorrect instructions.

Too technical. Not enough information. Not enough pictures. Any additional feedback? Submit feedback. Thank you for your feedback! The corresponding cell ranges must always be symmetrical : For example, if the second array contains one row or a value less than the first range, an error message is displayed.

The obvious advantage is that you can save several calculation steps by linking multiplication and addition. The function offers several tricks that are interesting for more complex tasks. It lets you choose which values in the matrices are to be added to the calculation. All you have to do is modify the actual syntax of the function somewhat:.

We have added a comparison to the first argument. The relevant cells are only multiplied and added to the total if the details correspond to the value in cell A Besides the equal sign that performs the comparison, multiplication signs have been added. In the normal syntax, the ranges can be separated simply using a comma.

Excel often already knows how to handle the data. However, if you include a comparison, the function requires clear identification of the calculation operation. Otherwise, an error occurs. You can also use an asterisk for multiplication. The following screenshot shows a similar formula in action:. In many situations, you might need to conditionally count or sum cells with AND logic and OR logic at a time.

Even in the latest versions of Excel, the IFs series of functions is not capable of that. To count how many times Apples and Lemons were sold in the North region, make a formula with the following logic:. To make the formulas a bit more compact, you can type the variables in separate cells - Region in F1 and Items in F2 and H2 - and refer to those cells in your formula:.

I believe at this point you won't have any difficulties with understanding the formula logic. If someone needs a detailed explanation, please check out the following tutorial: Calculating weighted average in Excel. What advantages does this gives to you? If any array argument contains non-numeric values, they will be treated as zeros.

In most cases, you'd need to convert them to 1 and 0 by using the double unary operator