Functions for choosing a sum based on two cells meeting the criteria

[titchestiny]

[h=2]Hi,

I'm trying to use a formula to select the correct calculation based on the criteria of two different cells. For example;

[TABLE="width: 375"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]29[/TD]
[TD]EXCEED[/TD]
[TD]EXCEED[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]27[/TD]
[TD]EXCEED[/TD]
[TD]YES[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]22[/TD]
[TD]YES[/TD]
[TD]YES[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]21[/TD]
[TD]YES[/TD]
[TD]NO[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Where B1 and C1 = Exceed, in D1 calculate A1+4, otherwise A1+3.

I've tried using combinations of the IF and AND functions, but neither will except the + symbol. Apologies if my question is particularly dumb. I know I'm missing something - I just can see it!

Any help on this would be greatly appreciated.

Thanks,

Tt

p.s. I'm using Excel 2010[/h]

 

[Aladin Akyurek]

D1, copied down:

Either...

=IF(AND(B1="EXCEED",C1="EXCEED"),A1+4,A1+3)

Or...

=IF(OR(B1="EXCEED",C1="EXCEED"),A1+4,A1+3)

 

[RobertCotton]

Try this formula =IF(AND(B1="exceed",C1="exceed"),A1+4,A1+3) it should do what you have asked for.

 

[bschwartz]

Or this:

Option 1: =IF(COUNTIF(B1:C1,"EXCEED")=2,A1+4,A1+3)
Option 2: =IF(B1&C1=REPT("EXCEED",2),A1+4,A1+3)

 

[titchestiny]

Hi Robert and Aladin,

Thank you for posting the formula - it worked a treat!

It was the formula that I originally used, but I couldn't get it to work. When you posted I realised that I missed the comma after the brackets. I knew something was up and that the formula should have worked, but I just couldn't see my error!

Thank you so much! I really appreciate your help.

Tt

 

[titchestiny]

Hi Ben,

Thank you for posting. I have made the formula work now. I was missing a comma! Feel very silly

Tt

 

[bschwartz]

Hi Ben,

Thank you for posting. I have made the formula work now. I was missing a comma! Feel very silly

Tt

Yeah, I was just posting some unusual alternatives for the heck of it.

I know your question was already answered.

 

[titchestiny]

Yeah, I was just posting some unusual alternatives for the heck of it.

I know your question was already answered.

Thanks - I appreciate it. I wish I had your excel skills - you seem to know your stuff! Often feel that I'm asking questions that would make guys like you roll their eyes and think 'idiot'!

Thank you for all your support. It means a lot.

Tt

 

[bschwartz]

Thanks - I appreciate it. I wish I had your excel skills - you seem to know your stuff! Often feel that I'm asking questions that would make guys like you roll their eyes and think 'idiot'!

Thank you for all your support. It means a lot.

Tt

Honestly, we're all learning here, especially me. Don't think that you questions are too stupid to ask.

The main thing is to be smart enough to ask for help when you need it.