How to add multiple criteria to a sumproduct formula.

[necht_angel]

Hello again all, I have been working on this sheet all day, and have a final challenge for excel. I'm not even sure if it can be done, but what I am trying to do is add in to an already complex formula (for me anyway!), so additional instructions. Basically I want the figure that is being carried accross to be carried across BUT with certain deductions and additions made to that figure.

Once again, for me trying to explain what I want is almost impossible as I am very green when it comes to these things (although I didn't think so until I first visited this site!!!)

I have attached a much used and updated example, and I must thank the two superstars who have helped get me this far on this spreadsheet q****c and T******s (not sure it was polite to put your names!)

Thanks guys for any pearls of excel wisdow thrown my way.

Kate

[daddylonglegs]

Hello Kate, how did you calculate that final amount of £15,561 based on a start amount of £14,000. Are you deducting 5% and then deducting 2% of that new amount or the original amount - also the 20%....is that a percentage added after the discounts, I can't get my figures to align with yours......

[necht_angel]

Hello Kate, how did you calculate that final amount of £15,561 based on a start amount of £14,000. Are you deducting 5% and then deducting 2% of that new amount or the original amount - also the 20%....is that a percentage added after the discounts, I can't get my figures to align with yours......

Hiya, daddylonglegs,

Yes it's less the 5%, then the balance less the 2.5%, then the balance of that +20% VAT although the percentages are subject to change, so the formula needs to take into account the cells the % are in (if that makes any sense lol)

Kate

[daddylonglegs]

You've actually got 2% as the discount not 2.5% so that figure will be slightly different but try this formula in H26 copied across and down

=SUMPRODUCT(--((H$25-$H$5:$V$5)>$F26),$H$18:$V$18)*(1-$C26)*(1-$D26)*(1+$E26)-SUM($G26:G26)

[necht_angel]

Seriously! There is me thinking I'm possibly asking excel to do something impossible, and you give me an answer in a few minutes! I love this site!

Thanks so much, thats fab, truly appreciate your help.

Kate

[necht_angel]

Sorry one last problem. When I drag down $H$18:$V$18) stays at 18 all the time, is there a way for the 18 to increase by one on the drag down, but the h & v to stay the same.,

[daddylonglegs]

The $ signs are used to "fix" the range (make it "absolute" instead of "relative"), e.g. a $ in front of the column number makes the column absolute and one in from of the row numbers makes that absolute.....so in your case you probably need to take out the red $s in $H$18:$V$18...so that would make the formula like this

=SUMPRODUCT(--((H$25-$H$5:$V$5)>$F26),$H18:$V18)*(1-$C26)*(1-$D26)*(1+$E26)-SUM($G26:G26)

[necht_angel]

Thanks again, that has just saved me hours of manually changing it all lol

Kate