IF D2 = Yes, D3 = Yes & D4 = Yes then D5 = +1 [Formula Help]

[LGIreland]

Hi,

I'm currently trying to get a formula going where if 3 values are true it adds +1 to a cell. For example;

If D2 = Yes, D3 = Yes & D4 = Yes then D5 = +1 (D5 will continuously have data added to it so it should add up i.e. if I done the same formula elsewhere and the same values matched twice then the value should equal 2)

I've been using =IF but I can't seem to perfect the formula to do what I need it to.

Any help would be appreciated.

 

[mikerickson]

As I understand your request, whenever D2:D4 all equal Yes, then you want D5 to be incremented by one.

Native Excel formulas can not do that.
One would need VBA for that.

 

[Mel Smith]

Welcome to the Board.

Try in D6: =if(D2="yes", if(D3="yes", if(D4="yes", D5+1, D5)))

I haven't tried this out...

 

[LGIreland]

As I understand your request, whenever D2:D4 all equal Yes, then you want D5 to be incremented by one.

Yep that's correct.

VBA? - Please advise further.

 

[mikerickson]

I advise that you redesign your spreadsheet. Perhaps by making entries on different rows.
Rather than continuously changing the values of D2:D4, put the current values in A1:C1, the next set of values in A2:C2 etc.

 

[LGIreland]

Welcome to the Board.

Try in D6: =if(D2="yes", if(D3="yes", if(D4="yes", D5+1, D5)))

I haven't tried this out...

I just tried the above formula and it is saying false. =IF('.Nov'!D2="pid",IF('.Nov'!G2="f",IF('.Nov'!H2="n",IF('.Nov'!I2="uk",November!D3+1,D3))))

 

[Yourself93]

Try this

=IF(AND('.Nov'!D2="pid",'.Nov'!G2="f",'.Nov'!H2="n",'.Nov'!I2="uk"),'November!'D3+1,'November!'D3)

edit: The one above (that the other person gave) is giving false because you are giving no answer if the outcome is false for the first 3 arguments. It would work if you put it as the below (I think anyway)

=IF('.Nov'!D2="pid",IF('.Nov'!G2="f",IF('.Nov'!H2="n",IF('.Nov'!I2="uk",November!D3+1,D3),D3),D3),D3)

 

[LGIreland]

Try this

=IF(AND('.Nov'!D2="pid",'.Nov'!G2="f",'.Nov'!H2="n",'.Nov'!I2="uk"),'November!'D3+1,'November!'D3)

edit: The one above (that the other person gave) is giving false because you are giving no answer if the outcome is false for the first 3 arguments. It would work if you put it as the below (I think anyway)

=IF('.Nov'!D2="pid",IF('.Nov'!G2="f",IF('.Nov'!H2="n",IF('.Nov'!I2="uk",November!D3+1,D3),D3),D3),D3)

Thanks this definitely helped since it is showing a value now (which is one) however when I apply the same formula to other cells it refuses to add another 1 to the value to total more than 1. I have also tried using this forumla instead: =IF('.Nov'!D2:D33="PID",IF('.Nov'!G2:G33="F",IF('.Nov'!H2:H33="N",IF('.Nov'!I2:I33="UK",+'.Nov'!V3,+'.Nov'!V4)+'.Nov'!V4)+'.Nov'!V4+'.Nov'!V4)+'.Nov'!V4) in an attempt to try and collate more data efficiently unfortunately that may have been a too simple solution! Any further ideas are greatly valued.

 

[LGIreland]

This is what I am working with: Imgur: The most awesome images on the Internet

This will then talk to another sheet if the information given is true (hopefully multiple times in the example shown above)

 

[LGIreland]

Bump - Apologies but I really need some advice!