'Sumproduct, Multiple Options & Blank Values
I am currently trying to produce a filter/summary for a data set using Sumproduct. The data is a list of codes, regions and managers each with a row value (£). Alongside this detail there is an account code summary, with a "filter" area to enter a region and/or manager.
The method seems to work when the filter has values entered for both region and manager, but if element is blank I would like all entries associated with the one element blank e.g. if the west area is selected, but the manager is left blank, all of the values for west, irrespective of manager would show as the total. Currently this scenario totals only blank manager values associated with the west - as expected. Obviously the reverse is true when filtering on a manager with the region left blank.
Is there any way around this using sumproduct? I've tried combing with if statements, but none seem to work.
Solution 1:[1]
If you're using Excel 2019 or earlier then this will have to be entered as an array formula
=SUMPRODUCT(--(($A$11:$A$24=$A4)*--($B$11:$B$24=$E$4)*--(IF(ISBLANK($F$4),ISNUMBER(ROW($C$11:$C$24)),$C$11:$C$24=$F$4)))*$D$11:$D$24)
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | Spectral Instance |