array2<\/b> and all other arrays onward are optional inputs. The function will multiply the components of all the arrays and then sum up the results<\/li>\n<\/ul>\n[\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
How to use SUMPRODUCT in Excel<\/h3>\n
I\u2019ll start off by showing a basic example of the SUMPRODUCT Excel function. Like I mentioned before, the function is primarily used to multiply arrays and sum the results.<\/p>\n
Take a look at the example below. I have some sales data from a fruit stand.<\/p>\n
[\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]To find the total revenue for the day, I need to multiply the price and the quantity for each item. Once I do that, I can add up the results, which give me the total revenue.<\/p>\n
I did this in column F, but you can see that this method requires a new column and a bunch of individual calculations. The SUMPRODUCT function is a much quicker way to solve this!<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/02\/1.-Sumproduct-revenue-example.png” alt=”sumproduct excel 1″ title_text=”1. Sumproduct revenue example” align=”center” _builder_version=”4.23.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
By typing =SUMPRODUCT(B2:B7,C2:C7)<\/b> I instruct the function to multiply each quantity with the corresponding price, and add up the total. This gives me the revenue all in one cell!<\/p>\n
I added the longer calculations in column F so you can see that the result is the same \u2013 SUMPRODUCT is just much faster.<\/p>\n
[\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Using SUMPRODUCT to count<\/h4>\n
When you input multiple arrays, SUMPRODUCT will multiply and sum them. But what if you only input one array? As you saw in the syntax, only the first array is required for the function to work.<\/p>\n
When you only input one array, the function will simply add up that array. You can see that in the screenshot below, where I use the function =SUMPRODUCT(B2:B7)<\/b> to add up the total quantity of items sold.<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/02\/2.-Sumproduct-quantity-example.png” alt=”sumproduct excel 2″ title_text=”2. Sumproduct quantity example” align=”center” _builder_version=”4.23.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
In this case, SUMPRODUCT works just like the SUM function!<\/p>\n
[\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Combining with other functions<\/h4>\n
One of the benefits of the SUMPRODUCT function is that you can combine it with other functions.<\/p>\n
In the example below, I combine it with the LEN function to find the total number of characters with the formula =SUMPRODUCT(LEN(A2:A7))<\/b>.<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/02\/3.-Sumproduct-characters-example.png” alt=”sumproduct excel 3″ title_text=”3. Sumproduct characters example” align=”center” _builder_version=”4.23.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Other operators<\/h4>\n
Based on its name, you may think the SUMPRODUCT function can only do multiplication between arrays. However, the function can also do arithmetic operations (*, \/, +, -) between arrays. The constant theme is that it always performs the operation across the arrays, and then adds up the total.<\/p>\n
Take a look at the example below. I use the SUMPRODUCT function to add up the Price and Tax columns, then multiply by the quantity. I do this with the formula =SUMPRODUCT(B2:B7*(C2:C7+D2:D7))<\/b>.\/p>[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/02\/4.-Sumproduct-with-addition.png” alt=”sumproduct excel 4″ title_text=”4. Sumproduct with addition” align=”center” _builder_version=”4.23.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Tip: to specify which operation you want to perform, just add the sign between the arrays instead of using a comma. It\u2019s also a good idea to use brackets to make sure it does the operations in the desired order.<\/p>\n
[\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Count with text<\/h4>\n
I\u2019ve used SUMPRODUCT with arrays of numbers, but now I\u2019ll demonstrate how you can use it with text.<\/p>\n
In this example, I use the Item column as my array, but I also specify that I only want cells with the text string \u201cBanana\u201d (I added another row of bananas just for this example).<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/02\/5.-Sumproduct-count-text.png” alt=”sumproduct excel 5″ title_text=”5. Sumproduct count text” align=”center” _builder_version=”4.23.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
You\u2019ll notice something weird about the formula used here, =SUMPRODUCT(–(A2:A8=”Banana”))<\/b>. It has two negative signs (–) before the brackets. Don\u2019t get too hung up on this \u2013 it\u2019s just what the formula needs to compute the results I want.<\/p>\n
When SUMPRODUCT evaluates the expression A2:A8=\u201dBanana\u201d it goes down the column and assigns a TRUE or FALSE to each cell. To sum these, I need to convert them from TRUE\/FALSE to a number.<\/p>\n
The \u201c–\u201c is like multiplying by -1 twice: once to convert the TRUE\/FALSE into a number, and another time to cancel out the negative. So TRUE becomes 1, and FALSE becomes 0. The function then adds these up, and correctly indicates that there are 2 Banana entries.<\/p>\n
Note that you could achieve the same effect by using 1* instead of — (i.e. multiply by 1 instead of by -1 twice), but it\u2019s a bit quicker to type the two dashes.<\/p>\n
[\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
SUMPRODUCT with text string and multiple arrays<\/h4>\n
Now I\u2019ll combine some of the previous examples. Here, I want the total revenue from selling bananas. I have to use 3 different arrays here.<\/p>\n
The first looks at the text strings in the Item column and tells the function to only consider bananas (by assigning them a 1 and the rest a 0). Then it multiplies this by the price and quantity, and adds up the result to give the total revenue from bananas.<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/02\/6.-Sumproduct-count-and-multiply.png” alt=”sumproduct excel 6″ title_text=”6. Sumproduct count and multiply” align=”center” _builder_version=”4.23.1″ _module_preset=”default” hover_enabled=”0″ global_colors_info=”{}” theme_builder_area=”post_content” sticky_enabled=”0″][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
The formula here is =SUMPRODUCT((A2:A8=”Banana”)*B2:B8*C2:C8)<\/b>. Note that unlike the last example, I don\u2019t need a — before the first array. That\u2019s because the TRUE\/FALSE generated by A2:A8=\u201dBanana\u201d is converted to a number when multiplied with the other arrays. So I only needed that trick when it was for just one array!<\/p>\n
This example really demonstrates the power of the SUMPRODUCT function. It performs the operations that I want on each entry in my data, and also selectively filters this to give only the results from the items I want to include. All from one formula!<\/p>\n
I hope that you now see the utility of the SUMPRODUCT function, and how to use it to perform complex calculations with one simple formula!<\/p>\n
[\/et_pb_text][\/et_pb_column][\/et_pb_row][\/et_pb_section]<\/p>\n","protected":false},"excerpt":{"rendered":"
You might think the SUMPRODUCT Excel function is pretty boring at first glance, but the function is actually extremely useful and versatile. While its main purpose is to multiply and add up data in arrays, you can also use it for a wide range of more complicated applications. With that being said, I\u2019ll dive right […]<\/p>\n","protected":false},"author":40,"featured_media":31429,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"_et_pb_use_builder":"on","_et_pb_old_content":"","_et_gb_content_width":"","footnotes":""},"categories":[65],"tags":[39,48,28],"class_list":["post-31427","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-excel-functions","tag-connections-t","tag-excel","tag-spreadsheets"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/posts\/31427","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/users\/40"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/comments?post=31427"}],"version-history":[{"count":0,"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/posts\/31427\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/media\/31429"}],"wp:attachment":[{"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/media?parent=31427"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/categories?post=31427"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/tags?post=31427"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}