Take a look at the example below. Here I’ve got a list of items, their prices, quantities, and total sale prices.<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/06\/SUMPRODUCT-FORMULA-GOOGLE-SHEETS.png” alt=”SUMPRODUCT FORMULA GOOGLE SHEETS 1SUMPRODUCT FORMULA GOOGLE SHEETS 1″ title_text=”SUMPRODUCT FORMULA GOOGLE SHEETS 1″ align=”center” module_class=”sheetgo-post-no-shadow-img” _builder_version=”4.22.2″ width=”100%” 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
I calculated the corresponding sale prices in column D<\/strong>, i.e. cells D2 through to D6.<\/p>\n To do this, I used multiplication functions to multiply each price by its quantity.<\/p>\n Then, I used the SUM function to calculate the total sales price across all the items (cell D7).<\/p>\n This is a rather lengthy process. Fortunately, there’s a much simpler alternative: the SUMPRODUCT function.<\/p>\n [\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.16″ border_width_left=”4px” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n =SUMPRODUCT(array1, [array2, \u2026])<\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n [\/et_pb_text][et_pb_text admin_label=”Connections T – One tool – Horizontal” module_class=”sheetgo-post-no-shadow-img md2-contained-button vertical-banner-container” _builder_version=”4.27.4″ _module_preset=”default” background_color=”#f2f7ff” max_width=”700px” module_alignment=”center” max_height=”300px” custom_margin=”20px|0px|20px|0px|true|true” custom_padding=”25px|25px|25px|25px|true|true” sticky_limit_bottom=”section” border_radii=”on|20px|20px|20px|20px” border_width_all=”1px” border_color_all=”#d9e7ff” global_colors_info=”{}” global_module=”255434″ theme_builder_area=”post_content”]<\/p>\n One tool to merge, split, and filter all your spreadsheet data<\/span><\/p>\n Find out how<\/a><\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n In the example above, I used the SUM function to get the results I wanted.<\/p>\n Here’s how to carry out the same calculation using the more efficient SUMPRODUCT function.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/06\/SUMPRODUCT-FORMULA-GOOGLE-SHEETS-2.png” alt=”SUMPRODUCT FORMULA GOOGLE SHEETS 2″ title_text=”SUMPRODUCT FORMULA GOOGLE SHEETS 2″ align=”center” module_class=”sheetgo-post-no-shadow-img” _builder_version=”4.22.2″ width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n In the snapshot above, I keyed in the SUMPRODUCT function into cell D8 by typing =SUMPRODUCT(B2:B6,C2:C6)<\/strong>.<\/p>\n Compare the value it returned with that in cell D7: it’s exactly the same.<\/p>\n The range B2:B6 is the input for array1, while C2:C6 is the input for array2. SUMPRODUCT multiplies and adds up the values all in one step \u2013 much easier!<\/p>\n While this is already much handier than the former approach, it gets even better when the source data set is altered, especially when you insert new rows.<\/p>\n I\u2019ll now try inserting a new record between the 4th and 5th rows:<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/06\/SUMPRODUCT-FORMULA-GOOGLE-SHEETS-3.png” alt=”SUMPRODUCT FORMULA GOOGLE SHEETS 3″ title_text=”SUMPRODUCT FORMULA GOOGLE SHEETS 3″ align=”center” module_class=”sheetgo-post-no-shadow-img” _builder_version=”4.22.2″ width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n You will notice that the SUMPRODUCT function is now relocated to cell D9. It automatically adjusted the calculation for the latest modification, while the SUM function did not change.<\/p>\n If you were to make the SUM function work, you would need to update the function in D5 and repeat this every time you add a new row, which could quickly become a time-consuming job.<\/p>\n So far I\u2019ve used the SUMPRODUCT function to multiply and add up two arrays. But you can use the SUMPRODUCT function with as many arrays as you want!<\/p>\n In the example below I\u2019ve added a third column that shows the % commission paid for each product sold.<\/p>\n Using the SUMPRODUCT function, I can quickly multiply all three columns together for each item, then add up the total. I\u2019ve done this by typing =sumproduct(B2:B6,C2:C6,D2:D6)<\/strong>.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/06\/SUMPRODUCT-FORMULA-GOOGLE-SHEETS-4.png” alt=”SUMPRODUCT FORMULA GOOGLE SHEETS 4″ title_text=”SUMPRODUCT FORMULA GOOGLE SHEETS 4″ align=”center” module_class=”sheetgo-post-no-shadow-img” _builder_version=”4.22.2″ width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Interestingly, you can also use the SUMPRODUCT function to sum items that meet certain criteria. This is similar to the COUNTIFS function<\/a>.<\/p>\n I\u2019ll demonstrate that in an example below. The function counts (or rather sums) the instances where all the array conditions evaluate to TRUE.<\/p>\n Here, I\u2019m asking for the number of items that meet the three following criteria:<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/06\/SUMPRODUCT-FORMULA-GOOGLE-SHEETS-5.png” alt=”SUMPRODUCT FORMULA GOOGLE SHEETS 5″ title_text=”SUMPRODUCT FORMULA GOOGLE SHEETS 5″ align=”center” module_class=”sheetgo-post-no-shadow-img” _builder_version=”4.22.2″ width=”100%” 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 As you can see, the SUMPRODUCT function searches the dataset and tells me that there are 2 items that meet these criteria.<\/p>\n The logic here may be confusing at first, but remember that in Google Sheets, True = 1 and False = 0. So when you use the SUMPRODUCT function with conditions that are True or False, you\u2019re really just multiplying 1\u2019s and 0\u2019s. Of course, any False value (in other words, a 0) will make the entire entry 0. That\u2019s why all the criteria must be true for the item to be counted.<\/p>\n To better illustrate this, I\u2019ve included the table below.<\/p>\n Note that the product of TRUE, TRUE, and TRUE will be 1, whereas the product of any combination that involves a FALSE evaluates to 0.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/06\/SUMPRODUCT-FORMULA-GOOGLE-SHEETS-6.png” alt=”SUMPRODUCT FORMULA GOOGLE SHEETS 6″ title_text=”SUMPRODUCT FORMULA GOOGLE SHEETS 6″ module_class=”sheetgo-post-no-shadow-img” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” 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 Once you understand how SUMPRODUCT treats true and false values, you can experiment with more complex uses of the function.<\/p>\n You can use other functions to generate true or false for each item based on whatever criteria you want!<\/p>\n In the example below I’ll show you one way of doing that.<\/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 I’ll expand on the sales commission example. Let\u2019s say that now, only items with an item number containing a\u00a02<\/strong>\u00a0will receive commission. The commission for the other items has been waived.<\/p>\n To ask the function to calculate this, I\u2019ve added a new column in which I use the ISNUMBER<\/a> and SEARCH<\/a> functions to determine whether there is a 2 in the item number (I do this by typing the formula =isnumber(search(2,A2))<\/strong>).<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/06\/SUMPRODUCT-FORMULA-GOOGLE-SHEETS-7.png” alt=”SUMPRODUCT FORMULA GOOGLE SHEETS 7″ title_text=”SUMPRODUCT FORMULA GOOGLE SHEETS 7″ module_class=”sheetgo-post-no-shadow-img” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” 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 Now I can do a SUMPRODUCT with 4 arrays, and the False values (i.e. 0\u2019s) will eliminate the commission for the products without a 2 in their item number \u2013 leaving me with only the relevant products being included in the calculation.<\/p>\n As you can see, you can use the SUMPRODUCT function to accomplish both simple and complex tasks very efficiently!<\/p>\n Check out our other formulas posts below!<\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.22.2″ background_color=”#f9f9ff” custom_margin=”40px||40px||false|false” custom_padding=”15px|25px|15px|25px|true|true” border_width_left=”3px” border_color_left=”#808e95″ saved_tabs=”all” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Editor\u2019s note<\/strong>: This is a revised version of a previous post that has been updated for accuracy and comprehensiveness.<\/em><\/p>\n [\/et_pb_text][\/et_pb_column][\/et_pb_row][\/et_pb_section]<\/p>\n","protected":false},"excerpt":{"rendered":" The SUMPRODUCT function in Google Sheets multiplies corresponding items within arrays of equal size and returns the sum of the results. This is useful in many situations where you need to multiply items across arrays and add them up. Take a look at the example below. Here I’ve got a list of items, their prices, […]<\/p>\n","protected":false},"author":40,"featured_media":27674,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"_et_pb_use_builder":"on","_et_pb_old_content":"The SUMPRODUCT<\/a> formula in Google Sheets multiplies corresponding items within the arrays of equal size and returns the sum of the results. We come across many such situations where we need to multiply items across arrays and add them up. For instance, please consider the following example.\n\nSyntax<\/h4>\n
\n
![\"\"](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2024\/09\/connections-top-processors-main.webp\")
<\/p>\n![\"39\"](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2024\/09\/Recommended-for-Google-Workspace-badge.webp\"){kind=icon}
<\/p>\nHow to use the SUMPRODUCT function<\/h3>\n
SUMPRODUCT with multiple arrays<\/h4>\n
Using SUMPRODUCT with conditions<\/h4>\n
\n
Complex SUMPRODUCT formulas<\/h4>\n
Looking for more Google Sheets tips?<\/h4>\n
![\"SUMPRODUCT](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/10\/frame-generic-31.png\")
\n\nWe have items, their prices, quantities and sale prices. We calculated the corresponding sale prices in column D, i.e. cells D2 through to D6. To do so, we used multiplication formulas. And, we used the SUM<\/a> formula to calculate the total sales price across all the items (cell D7). Certainly, this is a rather lengthy process. Fortunately, we have a much simpler alternative.\nSyntax<\/h3>\nSUMPRODUCT(array1, [array2, ...])<\/strong><\/span>\n
\n \t
Usage: SUMPRODUCT Formula<\/h3>\nWe have seen a use case above in which we used the SUM formula to achieve the desired output. Let us re-use the same data set to try the SUMPRODUCT formula.\n\n
![\"SUMPRODUCT](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/10\/frame-generic-32.png\")
\n\nIn the snapshot above, we keyed in the SUMPRODUCT formula in cell D8. Please compare the value it returned with that in the cell D7. It is exactly the same. The range B2:B6 is the input for array1<\/strong><\/span>, while C2:C6 is the input for array2<\/strong><\/span>.\n\nWhile this is already much handier than the former approach, this gets even better when the source data set is altered, especially when we insert new rows. Let's try inserting a new record between 4th and 5th rows.\n\n![\"SUMPRODUCT](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/10\/frame-generic-33.png\")
\n\nWe will notice the SUMPRODUCT formula is now relocated to cell C9. It automatically adjusted the calculation for the latest modification, while the SUM formula did not change. If we were to make the SUM formula work, we need to update the formula in D5 accordingly.\n\nInterestingly, we can also use this formula similar to that of the COUNTIFS formula. We demonstrate an example below. The formula is counting (or rather summing) the instances where all the array conditions evaluate to TRUE.\n\n![\"SUMPRODUCT](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/11\/sumproduct4.png\")
\n\nFor the purposes of better understanding the above case, please note that the product of TRUE, TRUE and TRUE will be 1. Whereas, the product of any combination that involves a FALSE evaluates to 0, as shown in the snapshot below.\n\n![\"SUMPRODUCT](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/10\/frame-generic-35.png\")
\n\n \n\n ","_et_gb_content_width":"","footnotes":""},"categories":[54],"tags":[39,28],"class_list":["post-3927","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-google-sheets-formulas","tag-connections-t","tag-spreadsheets"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/posts\/3927","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=3927"}],"version-history":[{"count":0,"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/posts\/3927\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/media\/27674"}],"wp:attachment":[{"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/media?parent=3927"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/categories?post=3927"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/tags?post=3927"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}