How to calculate sum of products in Excel: SUMPRODUCT and multiplication table

Key points of this article

To multiply two columns of data and then sum them, use =SUMPRODUCT(area 1,area 2) to complete it in one step without adding additional auxiliary columns.
SUMPRODUCT will treat the text as 0 and will not report an error. When the number is saved as text, the result will be smaller. Please check it before proceeding.
The number of rows in the two areas must be the same, otherwise #VALUE! will be returned and the area range can be corrected.
Use =$A2*B$1 to drag a formula horizontally or vertically to fill the multiplication table. The $ sign is key to lock rows or columns.
Use several known results to spot check, for example, 4×5 in the multiplication table should be 20, and then submit the table after comparison.

When many people make sales sheets, inventory sheets or quotations, they have to calculate the total amount of "unit price × quantity". Do the multiplication and addition line by line. When there are too many lines, it is easy to miss lines or misplace references. The SUMPRODUCT function in Excel can multiply and sum two columns in one step. It is one of the commonly used ways to write the multiplication and sum of excel function formulas.

This article is written for people who need to calculate products in batches but don’t want to manually process them line by line. In addition, two-dimensional multiplication such as the multiplication table can also be done with a formula, without having to enter it one by one. After reading, you will be able to use a formula to calculate the total, build a complete multiplication table, and know how to check whether the result is correct.

How to do sum of products? One sentence conclusion

You can directly use SUMPRODUCT to complete the sum of the products of two columns. Auxiliary columns are only needed when you need to view the amount row by row. The following table provides recommended practices for common situations.

your situation	Recommended practices	reason
Just a total amount, no intermediate results required	=SUMPRODUCT(unit price area, quantity area)	One formula completes multiplication and summation without occupying columns.
Need to see each amount line by line	Write =B2*C2 in the auxiliary column, and then use SUM to calculate the sum	Each row of results can be checked individually, making it easy to find errors.
I only want to count a certain product name or region	=SUMPRODUCT((condition area="East China")unit price areaquantity area)	Put conditional judgment and product in the same formula
There are gaps or text in the two columns of data	Use SUMPRODUCT directly	Non-digits are calculated as 0 and the calculation will not be interrupted, but please be careful about missing calculations.
The number of rows in the two areas is inconsistent	Correct the area first	If the number of rows is different, #VALUE! will be returned and no result can be obtained.

Before you start: Confirm these points first

Check the following items before taking action to avoid most wrong results.

Numbers are not real numbers. Data copied from other systems is often in text format. There may be a small green triangle in the upper left corner of the cell, or the numbers may be left aligned by default. SUMPRODUCT will not report an error when encountering text, but will count it as 0, so the result will be smaller. This is an easily overlooked problem.
Whether the number of rows in the two areas is consistent. For example, if B2:B10 has 9 lines and C2:C11 has 10 lines, an error will be reported during calculation. The range selection should start from the same line and end at the same line.
Whether the reference method will change during dragging. If the formula is to be copied to another location, the range should use an absolute reference, such as $B$2:$B$10, otherwise the range will shift as you drag.
Whether data will be appended to the table in the future. If the data will continue to increase, it is recommended to select the data area first and press Ctrl+T to convert to a table. The formula reference will expand with the table, so there is no need to manually modify the range every time.

Steps to use SUMPRODUCT to calculate the sum of the products of two columns

Let's take a product table as an example: column A is the product name, column B is the unit price, column C is the quantity, and the data is from row 2 to row 10.

Organize the data and confirm that columns B and C are numbers and there are no text numbers.
Select the cell that displays the total amount, such as E2.
Enter =SUMPRODUCT(B2:B10,C2:C10).
Press Enter, and E2 displays the sum of all "unit price × quantity".
Randomly select two or three rows, manually calculate the unit price times the quantity, and then compare it with the corresponding part of the result to confirm that the formula is quoted correctly.
If you only want to count a certain product name, enter =SUMPRODUCT((A2:A10="Notebook")B2:B10C2:C10). The condition result in the brackets is 1 or 0, which plays a filtering role.
If the data range changes, modify the range reference in the formula so that the number of rows in the two ranges remains the same.

Steps to build multiplication tables using formulas

The key to the multiplication tables is to write just one formula and then fill in to the right and down. The following steps build a multiplication table from 1 to 9.

Enter"×"in A1, and enter 1 to 9 in B1 to J1 as column titles.
Enter numbers 1 to 9 in A2 to A10 as row titles.
Enter =$A2*B$1 in B2. Among them, $A2 locks column A, and B$1 locks row 1, so that when copying to the right and down, each cell will get the correct row title and column title.
Press Enter. B2 displays the result of multiplying 1 by 1, which is 1.
Drag the fill handle in the lower right corner of B2 and drag it to the right to J2. The corresponding result will be automatically generated in line 2.
Select B2:J2, then drag down to row 10, and the entire multiplication table is generated.
Spot check F5, which corresponds to row 4 times column 5, and should display 20.

Check each item after completion

The results calculated by the formula must be checked item by item, especially when the data source is unclear.

Check items	How to see	What to do if there is a problem
Whether the result is abnormally small or 0	Select the original data and see if the numbers are aligned to the left and have a green triangle.	Convert text numbers to numeric values and recalculate
Does #VALUE! appear?	Check whether the starting and ending line numbers of the two areas are the same	Modify the area range to make the number of rows consistent
Whether newly added data is missed	Check whether the last row of the area covers all data	Expand the area, or convert the data into a table and then reference it
Is the multiplication table correct?	Spot check whether F5 is 20 and J10 is 81	Check if the $ symbol is written in the correct position
Is the formula consistent after filling?	Click on a few cells to see if the formula references change as expected	Re-enter the formula in the starting cell and fill it in uniformly

📊 Core comparison

There are usually three ways to write the sum of products in Excel, each of which is suitable for different situations.

Writing method	Suitable situation	Things to note
SUMPRODUCT function	To multiply and sum two or more columns, conditional statistics are required.	No special confirmation method is required. Text numbers will be calculated as 0. The format needs to be checked first.
Auxiliary column plus SUM	Need to see the amount line by line, or give it to someone else to check the details	Occupying one more column, the formula needs to be filled downwards, and it must be adjusted simultaneously when the number of rows changes.
Array formula =SUM(B2:B10*C2:C10)	Do simple sums in new versions that support dynamic arrays	The old version requires pressing a key combination to confirm it is an array formula. It is prone to errors when inputting and is not recommended as the first choice.

❓ Frequently Asked Questions (FAQ)

1. What is the difference between SUMPRODUCT and SUM array formulas?

The results of the two are the same, but SUMPRODUCT does not require special confirmation methods and can be used directly in older versions, so it is more reliable. When conditions need to be added, the writing method of SUMPRODUCT is also more intuitive.

2. Why is the result of SUMPRODUCT 0?

In most cases, the data are text numbers, and the function treats them as 0. First check whether the number is left aligned and whether there is a green triangle in the upper left corner of the cell, and then convert the text into a numerical value.

3. Can only half of the multiplication table be displayed?

Can. Add IF judgment to the formula, compare the row number and column number, and output the result only where needed. This requires the use of the ROW and COLUMN functions. Beginners can first complete the multiplication table before considering this step.

4. The table is in PDF, how to transfer the data to Excel for calculation?

You can first use the PDF editor of the OmniDoc desktop client to perform OCR on the scanned document, and then export the PDF to Excel. After exporting, check the numbers one by one, especially the decimal point and negative sign. After confirming that they are correct, use the above formula to calculate.

📝 Summary

Multiplying two columns of data and then summing them can be done with one formula: SUMPRODUCT; to build a multiplication table, the key is to lock the rows and columns with the $ symbol, and then fill them horizontally and vertically. You can avoid most errors by confirming the number format and the number of area rows before starting, and then spot-checking with several known results after calculation.

Open OmniDoc and work with your spreadsheet file