Forum Discussion
SUMPRODUCT with interruptions in the columns
Hello.
Can anyone advise - I would like to use SUMPRODUCT on two columns. Column H has a combination of money values and percentages in each cell (I have a price list some are monetary rates some are percentage uplifts on a net value in column V). I would like to SUMPRODUCT with column V but to accommodate the fact that I need to treat the percentage cells differently to the money cells I need to stop and start the formula - which I have done - however, when I enter values in the percentage sections the outturn value at the bottom seems to be doubling up on the percentage uplift - how do I correct this?
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.
6 Replies
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.
- Thank you.
- Dear sir
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.
