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

41 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

Connect to SAP R/3 and call custom FM from MS Excel

Article by: ExcelGuide

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.

Excel - Did You Know !

Article by: Shums

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…

Building Probability Models in Excel Part 5: Modeling an Investment Using Discrete Random Variables

Video by: Toby

The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

Building Probability Models in Excel Part 7: Modeling a Correlated Two-Fund Investment

Video by: Toby

The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …