Forum Discussion
SUMPRODUCT with interruptions in the columns
There seems to be some self-inflicted damage in terms of the way the problem is set up. The data analysis would be far more robust if entire columns could be used (as SergeiBaklan recommends)
= SUM(IF(base>0, base*(1+value)))
where 'value' is the uplift when a base value is specified.
Better still, you could rearrange the columns so that the costs are in one column and the uplifts (mainly zero) in the other.
= SUM(IF(uplift>0, (1+uplift)*base, 0))
where 'base' is the currency amount and 'uplift' is a percentage.
There seems to be some self-inflicted damage in terms of the way the problem is set up. The data analysis would be far more robust if entire columns could be used (as SergeiBaklan recommends)
= SUM(IF(base>0, base*(1+value)))
where 'value' is the uplift when a base value is specified.
Better still, you could rearrange the columns so that the costs are in one column and the uplifts (mainly zero) in the other.
= SUM(IF(uplift>0, (1+uplift)*base, 0))
where 'base' is the currency amount and 'uplift' is a percentage.
