Can you use the Sumproduct function with merged cells in Google Sheets? Absolutely! Just like Sumif, you can virtually unmerge and fill all the arrays in use in Sumproduct to get the correct result. In this article, I’ll walk you through how to do it in Google Sheets.
What Do We Want to Achieve?
With Sumproduct, we can get the sum of products all at once. Additionally, we can include criteria to skip certain rows. For example, let’s say we have a dataset that contains multiple products such as “ISMC 75,” “ISA 75X75X6,” and “ISMB 200” (country-specific structural steel items). We can use Sumproduct to get the sum of the product of any of these items or all of them. If we want to stick with specific items, we must use criteria within the formula. So, let’s dive into an example.
Now, let’s see what we want to achieve here. In the following example, some of the cells in the columns that contain items, unit, and unit weights are merged. But don’t worry, we can still make Sumproduct work its magic.
As you can see in the screenshot above, some cells are merged in the array used for evaluation. However, the Sumproduct formula in cell F2 returns the product sum correctly, based on the criteria in cell E2. For example, if I select “ISMB 200” (a structural steel item) from the drop-down in cell E2, the formula calculates as follows: =2*24.2+2.5*24.2.
Now, before I reveal the formula in cell F2, it’s important to know what formula you should use when you don’t have any merged cells. This is crucial before learning how to use Sumproduct with merged cells in Google Sheets.
Sumproduct with Merged Cells In Google Sheets (Formula and Explanation)
Assuming we have unmerged the data and formatted it as shown below:
We can use the following Sumproduct formula to get the sum of the product of the item selected in cell E2: =sumproduct( (A2:A10=E2)* B2:B10* D2:D10 ).
If you analyze this formula, you can understand that we need to deal with arrays 1 (cell range A2:A10) and 3 (cell range D2:D10). We have to virtually unmerge them and fill in the data. In other words, we replace A2:A10 and D2:D10 with virtual LOOKUP arrays. Here’s how you can do that:
A2:A10: lookup(row(A2:A10),if(len(A2:A10),row(A2:A10)),A2:A10)
D2:D10: lookup(row(A2:A10),if(len(D2:D10),row(A2:A10)),D2:D10)
Now let’s put it all together in the formula in cell F2:
=sumproduct( (lookup(row(A2:A10),if(len(A2:A10),row(A2:A10)),A2:A10)=E2)* B2:B10* lookup(row(A2:A10),if(len(D2:D10),row(A2:A10)),D2:D10) )
If you want to learn more about Lookup formulas, check out my detailed tutorial on how to fill merged cells down or to the right in Google Sheets.
Conclusion
When you want to include more columns, you can modify the cell range references/expressions in the above lookup formula. Simply replace all the cell references that are not enclosed within the ROW function with the corresponding cell range.
That’s all you need to know about using the Sumproduct function with merged cells in Google Sheets. Discover more about dealing with merged cells and access additional resources on our website, Crawlan.com. Happy sheeting!
Resources:
- Copy-Paste Merged Cells Without Blank Rows/Spaces in Google Sheets.
- How to Find the Cell Addresses of the Merged Cells in Google Sheets.
- Sort Vertically Merged Cells in Google Sheets (Workaround).
- Merge and Unmerge Cells and Preserve Values in Google Sheets.
- Sequence Numbering in Merged Cells In Google Sheets.
