For this we use theUNIQUE function.
This is what makes this solution fully dynamic.
Video:What is an array?
Generate the sums
We now have what we need to calculate the sums.
The formula in G5 is:
The firstargumentin SUMIFS issum_range.
This is the range that contains numbers to sum.
In this example, this is the Qty column in the table:
The other arguments arerange/criteriapairs.
Likewise, if rows are deleted from the table, spill ranges are reduced by UNIQUE as needed.
Legacy Excel workaround
Dynamic array formulasare new in Excel 365 and Excel 2021.
Dynamic array formulas are therefore easier to create and maintain.
Pivot Table option
Apivot tablewould also be agood way to solve this problemand would provide additional capabilities.
However, one drawback is that pivot tables need to be refreshed to show the latest data.
Formulas, on the other hand, update instantly when data changes.
