How to shorten a long nested formula

[laticsforlife]

I have the following formula that is part of a larger formula that I wrote but had to split into 3 sections due to the nesting limitations of Excel.

NB for the most part I am forced into using Excel XP from work.

The formula is as follows;

=IF(COUNTIF($N$6:$N$31,1)>Number_allowed,Note,IF(COUNTIF($N$6:$N$31,2)>Number_allowed,Note,IF(COUNTIF($N$6:$N$31,3)>Number_allowed,Note,IF(COUNTIF($N$6:$N$31,4)>Number_allowed,Note,IF(COUNTIF($N$6:$N$31,5)>Number_allowed,Note,IF(COUNTIF($N$6:$N$31,6)>Number_allowed,Note,IF(COUNTIF($N$6:$N$31,7)>Number_allowed,Note,"")))))))


The basic premise on each of 200 identical sheets is that N6:N31 contain 26 football team names (from a pool of 20 different names).


'Number_Allowed' refers to the maximum number of any one club that can be chosen (currently 2).


'Note' is the error message if someone chooses more than the maximum number allowed (it can change from year to year).


The number at the end of each Countif (from 1 through 7) are the Clubs names (and have been referenced elsewhere, i.e.


team Apple = 1
team Banana = 2

and so on up to team 20.


The formula above returns an error message if any of the cells have more than 2 of the 7 clubs searched for, but in 2 other adjacent cells I have similar formulas that do exactly the same for the other 13 clubs.


I cannot nest more than 7 clubs together, hence 3 formulas.


I would really like just one formula that looks for all 20 clubs at once (and one that does not mean me entering any other formulas in any other cells as it makes the whole spreadsheet very large as these 3 formula are currently repeated on 200 sheets).


Can anyone suggest a workaround?



An example is below

N6 = Apples
N7 = Pears
N8 = Grapes
N9 = Apples
N10 = Bananas
N11 = Cherries
N12 = Apples
N13 = Cherries


and so on up to


N31 = Apples


So in the above example 3 x Apples is an error.(it will be very unlikely that there are 2 different errors, and so it doesn't have to identify which item is in error, just that there is one)

 

[moonfish]

=IF(MAX(COUNTIF($N$6:$N$31,{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26}))>Number_allowed,Note,"")

Confirm this formula with CTRL+SHIFT+ENTER instead of just ENTER. If you've done this succesfully, Excel will enclose the formula in {}.

 

[laticsforlife]

Wow, superb and quick reply, many thanks.

(I've been stuck with that formula for 2 years and only just thought to ask someone outside of work for the answer).

 

[Peter_SSs]

Welcome to the MrExcel board!

Would this (also confirmed with Ctrl+Shift+Enter) work for you too?

=IF(MAX(COUNTIF(N6:N31,N6:N31))>Number_Allowed,Note,"")

Would another option be to use Data Validation to stop entry of more than the allowed number in the first place so you didn't need this formula to check?

 

[moonfish]

=IF(MAX(COUNTIF(N6:N31,N6:N31))>Number_Allowed,Note,"")

That's a beautiful solution, it's both faster and easier to read than mine. I'll have to remember that one.

 

[pgc01]

=IF(MAX(COUNTIF($N$6:$N$31,{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26}))>Number_allowed,Note,"")

Confirm this formula with CTRL+SHIFT+ENTER instead of just ENTER. If you've done this succesfully, Excel will enclose the formula in {}.

Hi moonfish

I don't think you need to confirm the formula with CSE. Please check.

 

[laticsforlife]

I used

{=IF(MAX(COUNTIF($J$6:$J$31,'Organisers Sheet'!$A$29:$A$48))>Number_allowed,Note,"")}

Which allowed me to remove a column of formulas which turned the club name into a number (as per the previous original), so it now looks for the names in column J in the Master data sheet which makes for easier annual changes (i.e. just change the master data and the rest will pick it up.)

It saved me 200 x 25 formula.

CSE was needed to make it work (or seems to be).

I couldn't (yet) figure out how to use Data Validation.

 

[Peter_SSs]

I couldn't (yet) figure out how to use Data Validation.

If the team names are being entered in the range N6:N31 and say the maximum number of one team allowed is 2 then ...

1. Select N6:N31.
2. Data|Validation|Settings tab|Allow: Custom|Formula: =COUNTIF(N$6:N$31,N6)<=2|If you want put an error message like "Oops - too many" on the Error Alert tab|OK

Now as you enter names in the range, it won't let you enter more than 2.

 

[laticsforlife]

Thanks I'll give it a go and see how I get on.

 

[moonfish]

Hi moonfish

I don't think you need to confirm the formula with CSE. Please check.

Apparently you're right! I thought any calculations involving arrays would require CSE. I was assuming the COUNTIF could not function without it but it seems the MAX function can handle arrays just fine.