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.
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.
