Next IFs and ANDs


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



Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
can you not alter to

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

you can only have 7 IFs
Upvote 0
I knew there was a limit to IFs... but when the 8th IF worked after removing the AND I got all confused and frustrated...
Upvote 0
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)
Upvote 0

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!
Upvote 0
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...
Upvote 0
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.
Upvote 0
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"))
Upvote 0
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.
Upvote 0

Forum statistics

Latest member
Rich Cohen

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