Jun 30 2021 01:59 AM
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?
Jun 30 2021 02:21 AM
Jun 30 2021 05:03 AM
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.
Jun 30 2021 01:52 PM
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.
Jul 01 2021 01:06 AM
Jul 01 2021 02:57 AM
SolutionThere 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 @Sergei Baklan 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.
Jul 01 2021 02:57 AM
SolutionThere 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 @Sergei Baklan 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.