Next IFs and ANDs

[MailmanTX]

This is starting to annoy me... and I can't figure it out for the life of me... I'm not an Excel NEWB - although I hate Pivot Tables because they're new and strange to me... Anyhew, here goes...

I'm trying to count a row of data based on two criteria (be super easy if I had REAL statistical software in the office... but I'll make do with what I have) for a worksheet full of a user's registration information...

1st criteria determines what kind of registration the user has
2nd criteria determines what kind of user the user is (i.e. are they a professional, student, blah blah blah).

so my formula I've come up with works, for the most part
IF(AND(COL-A="registration type",COL-B="user type"), 1, IF(... repeated count for other user types))

Now, I have 9 to 10 categories of "user type" data that I want to count, meaning I need at least 8 or 9 nested IF statements to accurately count them. The problem is Excel returns an error with the 8th IF, and highlights the "AND" portion of the statement. If I remove the AND the IF statement will work and return no error... is there some sort of limit to how many times I can place an "AND" statement in... cause it really shouldn't... anyhew...

IF(AND('Raw Data'!H2="RegType",'Raw Data'!I2="UserType1"),1,IF(AND('Raw Data'!H2="RegType",'Raw Data'!I2="UserType2"),2,IF(AND('Raw Data'!H2="RegType",'Raw Data'!I2="UserType3"),3,IF(AND('Raw Data'!H2="RegType",'Raw Data'!I2="UserType4"),4,IF(AND('Raw Data'!H2="RegType",'Raw Data'!I2="UserType5"),5,IF(AND('Raw Data'!H2="RegType",'Raw Data'!I2="UserType6"),6,IF(AND('Raw Data'!H2="RegType",'Raw Data'!I2="UserType7"),7,IF(AND('Raw Data'!H2="RegType",'Raw Data'!I2="UserType8"),8,9))))))))

If anyone can tell me why the 8th AND keeps failing that would be great. As I said, if I made it just IF(this, then that, or that) it works fine, but I still need to evaluate two criteria to accurately count this data.

Cheers!

L.

 

[DonkeyOte]

can you not alter to

=IF('Raw Data'!H2="Reg Type",0+LEFT('Raw Data'!I2,1),"")

you can only have 7 IFs

 

[MailmanTX]

I knew there was a limit to IFs... but when the 8th IF worked after removing the AND I got all confused and frustrated...

 

[Jonmo1]

Welcome to the board...

If anyone can tell me why the 8th AND keeps failing that would be great

Because Excel (Prior to version 2007) is limited to 7 Nested Functions.
Don't worry, there's almost ALWAYS a much better way to accomplish things like that..

Try this for example..

=MATCH('Raw Data'!H2&'Raw Data'!I2,{"RegTypeUserType1","RegTypeUserType2","RegTypeUserType3","RegTypeUserType4","RegTypeUserType5","RegTypeUserType6","RegTypeUserType7","RegTypeUserType8","RegTypeUserType9"},0)

 

[MailmanTX]

Laws... can you explain the 0+LEFT thing... I'm not familiar with that one bit...

 

[MailmanTX]

JonMo...

Thanks... not familiar with the MATCH function, but I have a feeling I'm going to like using it... I'll let you know how I fair!

 

[DonkeyOte]

should be 0+RIGHT(cell,1) - sorry.

Will take last character in string in the cell and convert to number.

given you had nine criteria and the last character of the string equates to the number you wanted to return this approach seemed the simplest...

 

[MailmanTX]

Well... not getting much love on this... As I'm looking to get tallies showing How Many [RegType] are [UserType1], [UserType 2] etc etc...

I know there has to be a way, but I can't get the RIGHT() or MATCH() function to work with what it is I'm trying to do (get a tally)... if anyone else has options please send'em my way.

 

[Jonmo1]

Do you mean...
How many of UserType1 There are in COLUMN I, Where COLUMN H = Registration Type?

=Sumproduct(--('Raw Data'!H2:H100="RegistrationType1"),--('Raw Data'!I2:I100="UserType1"))

 

[MailmanTX]

Yea...
I have two different Registration Types
and Nine different User Types (or profession)

The only reason I'm using Excel to manage this table is that it's a subset of a much larger database and I'm attempting to extract some logical information from this group that I've narrowed in on VIA DB querying.

Col H contains their Registration Type, where as Col I contains the User Type. So I want to be able to look at all those records that are RegistrationType 1 and tally up all the different user types so I can report something like this:

In Registration Type 1 there are X of User Type 1, XX or Type 2... blah blah blah... effectively I'm trying to use Excel for a little more marketing statistical analysis than I think it's geared to do - but it's all I have to work with at this time.