Ennumber of conditional IF statment

[sampath]

Hello,

I have to create tracking sheet for employee production, For that, can't create the above 32 condition of IF statement. I known only Limited conditional if statement.

Kindly suggest me for this.

Regards,
Sampath.S

 

[NARAYANK991]

Hi ,

Unless you give more details of your application and your requirement , and preferably upload a sample workbook with enough data , I doubt that you will get a precise answer fast.

Narayan

 

[sampath]

Hello NARAYANK991,

Thanks for your reply,

I have attached the example condition list file, when I using below If statement, Limited condition only applicable. but need above 30 conditions.

=IF(AND(A1="E",A1=S),"12",IF(AND(A1="E",A1="M"),"3",IF(AND(AH="C"),"6",IF(AND(AH=""),""))))

Please suggest any solution for this.

 

[Asheesh]

Hi Sampath - you can use upto 64 nested ifs in Excel 2007 and above..using If conditions is not the only solution..can you share the tracking sheet with couple of examples along with required output.

 

[sampath]

Hello Asheesh,

Thanks for your reply,

could you provide the nested if example formula. This is very helpful to customize for our requirement.

Regards,
Sampath.S

 

[Tiago MF]

Hi sampath

Please see the file attached. The desired output is on column E for 4 examples. This of course requires you to maintain the auxiliary table that has all the conditions somewhere on your file.

Also the formula might be a bit bigger then necessary so if anyone wants to step in and make it simpler, be my guest

BR,

 

[Deepak]

Hi sampath

Please see the file attached. The desired output is on column E for 4 examples. This of course requires you to maintain the auxiliary table that has all the conditions somewhere on your file.

Also the formula might be a bit bigger then necessary so if anyone wants to step in and make it simpler, be my guest

BR,

Hi,


Just optimize the formula!!

=INDEX($C$2:$C$31,MATCH(1,($A$2:$A$31=G2)*($B$2:$B$31=H2),0))

With CSE

 

[Tiago MF]

Nice one Deepak! I like how you used the conditions inside that Match for simple positioning. Pretty neat.

 

[sampath]

Hello Tiago MF,
When used this formula with table in another workbook, It can't working.
and during that formula copied that{} is hided.

Hello Deepak,

When using this formula in I2 cell and same time putting input data in G2 & H2.

It shows like "0" only.

Kindly solve such kind of clarification.

Thanks for your understanding.

Regards,
Sampath.S

 

[Deepak]

This is an array formula so not just press enter hit Ctrl+Shift+Enter.

 

[sampath]

Hello Deepak,

Thanks for your clarification, I need to know some more clarification about this formula.

Copied this formula and using in another cell, It is not working.

When copied this formula, I am not able to copy with {} symbol.

Kindly solve this issues.

Regards,
Sampath.S

 

[Deepak]

Hello Deepak,

Thanks for your clarification, I need to know some more clarification about this formula.

Copied this formula and using in another cell, It is not working.

When copied this formula, I am not able to copy with {} symbol.

Kindly solve this issues.

Regards,
Sampath.S

You don't have to copy {} just copy the formula or do the changes in range & then hit Ctrl+Shift+Enter.

Never try to copy {}

 

[Asheesh]

Hi - Non array solution -
SUMPRODUCT(($A$2:$A$31=G2)*($B$2:$B$31=H2)*($C$2:$C$31))

One more array solution

VLOOKUP(G2&H2,CHOOSE({1,2},$A$2:$A$31&$B$2:$B$31,$C$2:$C$31),2,0)

Check column I & J of the attached

 

[Khalid NGO]

=SUMIFS($C$2:$C$31,$A$2:$A$31,G2,$B$2:$B$31,H2)
simillar to Asheeh's Sumproduct

 

[Deepak]

Excel is excellent in itself ...

One more array!!

=INDEX($C$2:$C$31,MATCH($G2&$H2,$A$2:$A$31&$B$2:$B$31,0))


If the output is numeric then go with Asheesh / Khalid NGO non array solution as both are simple and clean else you must have to use array function.

So, now depends on @sampath how he kicks..