I have written the below sumproduct formula, which is referencing a table called UnitData. It is matching by two criteria, region and market territory, and then summing the applicable units.
SUMPRODUCT((UnitData[Region]=IF($A12="",UnitData[Region],$A12))*(UnitData[Market Territory]=IF($B12="",UnitData[Market Territory],$B12)),UnitData[Equivalent Units])
The value existing in A12 or B12 can either be specified, or left blank. If left blank, I want the sumproduct to sum all regions and/or all market territories. However, if I just leave blank, it tries to match a blank in the data table. So I was forced to add an IF statement that essentially points the data reference back to itself if A12 or B12 are blank.
This formula works, but I was wondering if there is a more efficient way to structure it so I don't have to specify the data criteria pointing back to itself.