Next IFs and ANDs

MailmanTX

New Member
Joined
Jul 3, 2008
Messages
6
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.
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
can you not alter to

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

you can only have 7 IFs
 

MailmanTX

New Member
Joined
Jul 3, 2008
Messages
6
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

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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..
Code:
=MATCH('Raw Data'!H2&'Raw Data'!I2,{"RegTypeUserType1","RegTypeUserType2","RegTypeUserType3","RegTypeUserType4","RegTypeUserType5","RegTypeUserType6","RegTypeUserType7","RegTypeUserType8","RegTypeUserType9"},0)
 

MailmanTX

New Member
Joined
Jul 3, 2008
Messages
6

ADVERTISEMENT

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

MailmanTX

New Member
Joined
Jul 3, 2008
Messages
6
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

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123

ADVERTISEMENT

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

New Member
Joined
Jul 3, 2008
Messages
6
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

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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

New Member
Joined
Jul 3, 2008
Messages
6
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,481
Messages
5,596,393
Members
414,063
Latest member
N_Bates

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top