Sorry if this is a very basic question - but I'm struggling how to find this on a google search.
Essentially what I need is a way to keep subtracting from a number if an condition is met.
I can do the IF statement fine for one cell, but am unsure how to keep deducting from the value.=IF(D9= "Y", 100-E9)
The screenshot below explains it better. Essentially if there is a 'Y' then the figure on the right should subtract from 100. If 'N' then nothing should happen.
Thanks for any help given in advance.enter image description here
Best Answer
Sure you can. Sounds like you want to use SUMIF combined with a subtraction.
For your case, the formula will likely be:=<start value>-SUMIF(<range of Y/N>, <condition>, <range of values>)
e.g. for a sheet with:
- either
YorNin column A, from A1 to A10 - numeric values in column B, from B1 to B10
- where the value in B1 is to be subtracted if A1 is
Y - the start value is 100
You would use =100-SUMIF(A1:A10, "=Y", B1:B10)