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.
Whatever your value is going to double, you can divide by 2 which value will be double
Anyway If you share the file, then i will do better.
Thank you for the response. I don't quite follow. Attached is a reduced version of the spreadsheet. The SUMPRODUCT formula should be returning £23 rather than £26 in this particular example.
First part of the formula
=SUMPRODUCT( (D3:D19:D24:D60:D66:D75:D88:D136:D142:D147:D159:D174:D177:D188:D192:D206),($C$3:$C$19:$C$24:$C$60:$C$66:$C$75:$C$86:$C$136:$C$142:$C$147:$C$159:$C$174:$C$177:$C$188:$C$192:$C$206) )is equivalent to
=SUMPRODUCT((D3:D206),($C$3:$C$206))Excel excludes nothing with notation you use, it takes entire range from first to last cell. With that SUMPRODUCT() returns 3, With 23 which gives the rest of the formula totally is 26.
You need to structure your data more carefully, i.e. add helper column which indicates which cells to multiply and use SUMPRODUCT() on entire range with it.
