Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

Solved

How would I modify this code to work?tells me in the first part i have to many arguements for this function. "11","15", etc.

Posted on 2016-09-28

Microsoft Excel

1 solution

Medium Priority

44 Views

Last Modified: 2016-09-28

IF(
AND(COUNTIF($AP2,"*11*","*15*","*17*","*20*","*49*","*50*", "*71*", "*72*" ),SUM(COUNTIF($AV2:$AZ2,{"*G0480*","*G0479*","*G0477*","*G0478*","*G0481*","*G0482*","*G0483*" }))>0),"Unable to resolve. Need InfoCode for LABOUTP. BMSD is mapped. Benefit Engine not mapped.",

How would I modify this code to work?tells me in the first part i have to many arguements for this function. "*11*","*15*", etc. I need it to work for everything listed above.

Thank you..

Comment

Question by:Culwatrnca11

[X]

Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

Help others & share knowledge
Earn cash & points
Learn & ask questions

Join The Community

5 Comments

LVL 27

Expert Comment

by:Glenn Ray

ID: 418203022016-09-28

Can you please re-type your formula exactly as it appears in your workbook? The original post has it split up in a way that doesn't make sense. There are no [value if true] or [value if false] arguments in your IF statement. You're also missing a closing } in your initial COUNTIF list.

Perhaps this will work:
=IF(AND(SUM(COUNTIF($AP2,{"*11*","*15*","*17*","*20*","*49*","*50*","*71*","*72*"}))>0,SUM(COUNTIF($AV2:$AZ2,{"*G0480*","*G0479*","*G0477*","*G0478*","*G0481*","*G0482*","*G0483*"}))>0),TRUE,FALSE)

replacing TRUE, FALSE with your intended actions.

-Glenn

Author Comment

by:Culwatrnca11

ID: 418203402016-09-28

Hi Glenn,

This is what the formula would look like.

=IF(AND(SUM(COUNTIF($AP2,{"*11*","*15*","*17*","*20*","*49*","*50*", "*71*", "*72*"}))>0),SUM(COUNTIF($AV2:$AZ2,{"*G0480*","*G0479*","*G0477*","*G0478*","*G0481*","*G0482*","*G0483*"}))>0),"Unable to resolve. Need InfoCode for LABOUTP. BMSD is mapped. Benefit Engine not mapped.","Please Review")

not working.

LVL 27

Accepted Solution

by:Glenn Ray

Glenn Ray earned 2000 total points

ID: 418203672016-09-28

Try this:
=IF(AND(SUM(COUNTIF($AP2,{"*11*","*15*","*17*","*20*","*49*","*50*","*71*","*72*"}))>0,SUM(COUNTIF($AV2:$AZ2,{"*G0480*","*G0479*","*G0477*","*G0478*","*G0481*","*G0482*","*G0483*"}))>0),"Unable to resolve. Need InfoCode for LABOUTP. BMSD is mapped. Benefit Engine not mapped.","Please Review")

You had an extra close paranthesis after the ">0" test.

Author Closing Comment

by:Culwatrnca11

ID: 418204052016-09-28

Perfect..... thank you Glenn for the speedy solution working perfectly.

LVL 27

Expert Comment

by:Glenn Ray

ID: 418204402016-09-28

You're welcome.

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

MS Excel - using the Hyperlink formula

Article by: Thomas

Do you use a spreadsheet like Microsoft's Excel? Have you ever wanted to link out to a non excel file on your computer or network drive? This is the way I found to do it!

Deleting Shapes - A Warning

Article by: Martin

This article describes a serious pitfall that can happen when deleting shapes using VBA.

Creating a Scrolling Chart in Excel

Video by: AnnieCushing

This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

Microsoft Excel

Binomial Distribution

Video by: Ed

Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…