New Contributor

# Subtracting figure based on text cell

Hi,

I'm trying to create a formula which will deduct 100000 from a figure in one cell based on another if the text is yes in one of 3 other cells, and create a total in another cell, can this be done e.g:

If O10, O11, or O13 and 014 or O15 are "yes", deduct 100000 from P27

2 Replies

# Re: Subtracting figure based on text cell

=IF(AND(COUNTIF(O10:O11,"yes")+COUNTIF(O13,"yes")=1,COUNTIF(O14:O15,"yes")=1),P27-100000,"")

You can try this formula if one of cells O10, O11 or O13 is yes and one of cells O14 or O15 is yes.

# Re: Subtracting figure based on text cell

Thanks for the reply.

Unfortunately, I can't make it work. I am trying to create a calculator where the value in P29 will be the result of a net value in P27 less 100,000 for each "yes" answer in O9 and O11 (I.e. if one is yes it deducts 100,000, both are yes it will deduct 200,000 and if both are no it will deduct 0 and give the net value). I also need to deduct a further 100,000 if O14 and O15 or O16 is yes.

Any help is greatly appreciated!